LINQ: Prefetching data from a second table

Question

Hi,

I'm trying to pre-fetch some foreign key data using a linq query. A quick example to explain my problem follows:

var results = (from c in _customers
               from ct in _customerTypes 
               where c.TypeId == ct.TypeId 
               select new Customer
                          {
                             CustomerId = c.CustomerId,
                             Name = c.Name,
                             TypeId = c.TypeId,
                             TypeName = ct.TypeName,  <-- Trying to Prefetch this
                          }).ToList();

The Customer class looks like:

[Table(Name = "Customers")]
public class Customer
{
   [Column(Name = "CustomerId", IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
   public int CustomerId { get; set; }

   [Column(Name = "Name")]
   public string Name { get; set; }

   [Column(Name = "TypeId")]
   public int TypeId { get; set;}

   public string TypeName { get; set; }

   public Confession (){}
}

However LINQ will not let you do this throwing a NotSupportedException with "Explicit construction of entity type 'Customer' in query is not allowed."

I'm clearly approaching this incorrectly. Any pointers in the right direction would be most helpfull.

Answer 1

As it says, you can't construct a Customer there.

The (arguably) easiest thing to do would be to create a new class that encapsulates the properties you need. You can get everything from the Customer class by doing it like this:

var results = (from c in _customers
               from ct in _customerTypes 
               where c.TypeId == ct.TypeId 
               select new
                      {
                         Customer = c,
                         TypeName = ct.TypeName
                      }).ToList();

Answer 2

If you want to do genuine pre-loading, you can do the following:

(This assumes there's a join between the Customer and CustomerType tables in your database, and that LINQ to SQL knows about it.)

MyDataContext dc = new MyDataContext(); // Use your application-specific DataContext class
DataLoadOptions loadOptions = new DataLoadOptions();
loadOptions.LoadWith<Customer>(c => c.CustomerType);
dc.LoadOptions = loadOptions;
var results = from c in dc.GetTable<Customer>() select c;

You could then access the TypeName thus (where c is a Customer in results):

c.CustomerType.TypeName;