How do I query navigation properites using linq to sql and ef

Question

Hi, I am trying to strongly type a query for 3 ef objects using linq to sql. There are one-to-many relationships with product and category. My classes contain navigation properties and look like this.

public partial class Product
{

public int ID {get;set;}
public string Name {get;set;}
public virtual ICollection<Group> NpGroup {get;set;}
}

public partial class Category
{
public int ID {get;set;}
public string Name {get;set;}
public virtual ICollection<Group> NpGroup {get;set;}
}

public partial class Group
{
public int ID {get;set;}
public int ProductID {get;set;}
public int CategoryID {get;set;}
public virtual Product NpProduct {get;set;}
public virtual Category NpCategory {get;set;}
}

Trying to avoid the string based .Include(), how would I construct a query that returned a group equal to ProductID "1" but also included the names of the product and category?

Something like:

var context = ObjectContext.CurrentObjectContext;
    var query = from c in context.Group
    where c.ProductID == 1
    //Include the names of the product and category of the group record (c.NpProduct.Name etc.)
    select c;

I am probably missing the trees through the forest but I can not seem to get the syntax of ObjectContext.LoadProperty (if that is the right way to go).

Any thoughts? Thanks.

Answer 1

First of all, i doubt you are using both L2SQL and EF, so try not to confuse people.

Anyways, with EF - there is two ways to load the navigational properties:

1 - Eager Loading

q.Include("NavPropertyName")

2 - Explicit Load

*After* running your above query - use q.NavPropertyName.Load()

Difference is option 2) causes 2 queries, option 1 causes an inner join on the FK.

I can sympathise with your reluctance to use Include because of the 'magic strings' - i'm not sure why the EF team didn't make them strongly typed, however im sure there was a good reason.

More info on Load/Include here.

HTH

Answer 2

I think we all hate to use the typed string in the .include() statement.

I've started to use a enum to represent the table name, just to avoid spelling errors, etc.

for my database of about 70 tables it took me 10 min. to create the enum and my linq is now something like this:

var context = ObjectContext.CurrentObjectContext; var query = from c in context.Group.Include(TableEnum.Category.ToString()) where c.ProductID == 1 select c;

Again, not perfect, but at least it's checked by the compiler