LINQ: prefetching data from a second table

Question

I am trying to pre-get some foreign key data using linq query. The following is a brief example of an explanation of my problem:

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 this:

 [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 allow you to throw this NotSupportedException with "Explicitly building the type of the Client object in the request is not allowed."

I am clearly approaching this wrong. Any pointers in the right direction would be most helpful.

Answer 1

As the saying goes, you cannot create a client there.

(possibly) the easiest way would be to create a new class that encapsulates the properties you need. You can get everything from the Customer class by doing the following:

 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 a genuine preload, you can do the following:

(This assumes a connection between the Customer and CustomerType tables in your database and what 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; 

Then you can access the TypeName this way (where c is Customer in the results ):

 c.CustomerType.TypeName;