Retrieve fewer table fields using Linq To Sql lambda expressions

Question

I'd like to know how to get a subset of data from a table using Linq To SQl Lambda expressions...

Let's say I have a table tbl_Product, with fields ProductId, ProductCode, ProductName, Description, WhyBuyCopy, how do I get only the first three fields when the others are not needed given that retrieving all data takes a second longer (checked using 'set statistics time on')?

One solution could be to create a partial class that extends that created by linq2sql, with only the fields needed, but I am trying to avoid that...

The other solution of course is to use

from p in base.dc.E_Products
select new E_Product
{
    ProductId = p.ProductId,
    ProductCode = p.ProductCode,
    etc
})

but I am very curious to know whether there is an equivalent lambda expression for the above code.

Thank you

Answer 1

The second solution you propose does not work with LINQ to SQL, because it won't allow you to create a new LINQ to SQL entity within a LINQ query, since this entity won't have change tracking.

Your best option is to use an anonymous type (if possible) or create a Data Transfer Object (DTO) with only those two fields:

public class ProductDto
{
    public ProductId { get; set; }
    public ProductCode { get; set; }
}

from p in base.dc.E_Products
select new ProductDto()
{
    ProductId = p.ProductId,
    ProductCode = p.ProductCode,
});