Whats the Efficient way to get data from db using LINQ To SQL or LINQ To Entities?

Question

When you run Linq to Sql or Linq to Entites to get a list of records it runs query to select all fields from a table. Is it an efficient solution. Lets say: I run this LINQ

dim lstCustomers = from c in db.Customers select c

it run query to get all fields from a table whether i need all fields or not. I am using asp.net with MVC so should i write this query in view (where i only need CustomerID and name)

    dim lstCustomers = from c in db.Customers _
                       select new Customer with { c.CustomerID, c.Name }

If i have to use 2nd query then whats the advantage of LINQ and Entity Framework. This thing i can do with SQL query (with different syntax)

Anyone can help?

Answer 1

First of all, LINQ queries are evaluated lazily. That means that single line doesn't do anything but itself, so I assume you actually iterate the results with For Each.

The answer to your first question is yes, all fields are retrieved from the database with the first statement.

Yes, but in order to use SQL directly, you'll have to manually create entity classes, manually retrieve data using SqlDataReader or something to achieve the level of abstraction LINQ provides in that line. That's lots of more work on your behalf. With LINQ to SQL, you don't even need to explicitly write code to open a connection to database.