Linq many to many include

Question

I have 3 entities

public class A
{ 
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<AB> ABs { get; set; }
}

public class AB
{
    public int AId { get; set; }

    public virtual A A { get; set; }

    public int BId { get; set; }

    public virtual B B { get; set; }
}

public class B
{
    public int Id { get; set; }
    public virtual ICollection<AB> ABs { get; set; }
}

I need to write the following SQL in Linq :

SELECT * FROM A, AB
WHERE
A.Name = 'name'
AND AB.BId = 1

I've tried:

DbContext.Set<A>().Include(a => a.ABs.Select(ab => ab.B).Where(b => b.Id == 1)).Single(a => a.Name == "name");

DbContext.Set<A>().Include(a => a.ABs.Where(ab => ab.BId == 1)).Single(a => a.Name == "name");

DbContext.Set<A>().Include(a => a.ABs.Select(ab => ab.BId == 1).Single(a => a.Name == "name");

DbContext.Set<A>().Include(a => a.ABs.Any(ab => ab.BId == 1)).Single(a => a.Name == "name");

I always get the same message :

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties.

I'd like to understand the error message.

My questions :

Is "public virtual ICollection ABs" a navigation property ?
What are dotted paths ?
What's the difference between "reference navigation properties" and "collection navigation properties" ?
What should I change to get my request to work ?

score 3 · Accepted Answer · edited May 23 '17 at 11:52

Is "public virtual ICollection ABs" a navigation property ?
What's the difference between "reference navigation properties" and "collection navigation properties" ?

Yes. A navigation property is a property by which you "navigate" from one entity to another. They come in two flavors. A.ABs is a collection navigation property, AB.B is a reference navigation property. The other properties, like A.Name, are called scalar properties.

What are dotted paths ?

Paths with a dot in it. But the message isn't really clear here. It applies to included properties nested in other navigation properties.

So collection navigation properties are included by a Select:

DbContext.Set<A>().Include(a => a.AB.Select(ab => ab.Cees))

(where Cees is a collection in AB)

And reference navigation properties are included by dots:

DbContext.Set<A>().Include(a => a.D.F)

(where D and F are references)

But to me that's not clear at all, because...

Include(a => a.ABs.Select(ab => ab.B))

... includes a reference by a Select. Well, it's hard to conceive clear and brief exception messages.

What should I change to get my request to work ?

The message is not too clear, but, worse, it doesn't tell what's wrong either. You do use navigation properties defined on the type!

Include(a => a.ABs.Select(ab => ab.B).Where(b => b.Id == 1))

But you can't have the Where in it. This is a notorious pitfall. The lambda syntax suggests that any LINQ method can be used in Includes, but in reality only Select and . are allowed to append navigation properties.

What you want is a often-requested feature: filtered Include. Presently, EF only supports including full collections. In EF 7, finally filtered Includes will be supported.

Until then, you have to use work-around, for instance this one.

score 0 · Answer 2 · edited Nov 15 '19 at 18:02

var ww = _context.Agentes.Include(a => a.AgentesObjetos.Select(ab => ab.Objeto)).ToList();

error InvalidOperationException: The Include property lambda expression a => {from AgenteObjeto ab in a.AgentesObjetos select [ab].Objeto} is invalid.

The expression should represent a property access: t => t.MyProperty.

To target navigations declared on derived types, specify an explicitly typed lambda parameter of the target type, E.g. (Derived d) => d.MyProperty.

For more information on this subject, including related data, see http://go.microsoft.com/fwlink/?LinkID=746393.

Linq many to many include

2 Answers2