I am a .NET developer with a lot of SQL Server 6.5 and onward experience. I have never been called a DBA but I have functioned in that role over the years.

If you search this site for "Entity Framework" you will find that most DBAs are not very excited about EF or ORM in general. Like most things, you can't stick your head in the sand and just be a coder and not worry about what kind of T-SQL the ORM is going to generate. You do need an appreciation for the structure of the database and the performance implications. However, we don't all work in places with guru MSSQL DBAs who know how to wire awesome stored procedures. Sometimes the DBAs are so busy they can barely keep up with the system administration stuff. Sometimes you have server freezes where you can only ALTER a stored procedure 5 times a year. Separating data access from business rules and user interface is a best practice and a new tool in your toolbox won't automatically prevent you from making bad decisions. So embedding the query, be it LINQ or T-SQL or whatever, inside your event handler for a button click is always possible.

ORM seems very promising because it should make code far more reusable and extensible. ORM + a good architecture for a piece of software + profiling & testing to see that the ORM is not doing something stupid on the SQL Server. The safest approach is probably to wait and see what happens with all this. And if you do try to use Entity Framework, do it on a small project, preferably something brand new.

LINQ to SQL is losing favor even among those who "are drunk on Microsoft Kool-Aid" because it doesn't support other back-end databases besides MSSQL... and other reasons you can easily find by Googling. For the moment, the big push is for EF and Microsoft really wants developers to start adopting it.