howt to Include related entities with filter condition using AsExpandable

Question

I want to include related entities with some filter condition. Is this possible ?? I don't want to write projection query for this. So I was trying to achieve this by below code...... but it's not working.

My Domain Object

public class UserRef : BaseModel
{
    public static readonly System.Linq.Expressions.Expression<Func<UserRef, ICollection<UserNewsLetterMap>>> UserNewsLetterExp =
        UserNewsLetterExp => UserNewsLetterExp.UserNewsLetterMaps;

    public UserRef()
    {
        UserNewsLetterMaps = new HashSet<UserNewsLetterMap>();
    }

    public int UserId { get; set; }

    public string UserName { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public virtual ICollection<UserNewsLetterMap> UserNewsLetterMaps { get; set; }

}

My Repository code

var user = this._context.UserRefs.AsExpandable()
            .Include(u => UserRef.UserNewsLetterExp.Invoke(u).Where(news => news.Subscribe).Select(news => news.DocTypeRef))
            .SingleOrDefault(u => u.UserName == userName);

What is the best practics or best solution for this?

Thanks in advance :)

Getting below error : Additional information: 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. — Pankaj Rawat, Oct 11 '16 at 16:19
This has been asked a million times. `Include` **cannot** be filtered - it's all or nothing. — Ivan Stoev, Oct 11 '16 at 16:32

score 1 · Accepted Answer · answered Oct 12 '16 at 17:35

As of now I found 2 solution for my problem 1. Projection query

var result = db.UserRef
.Select(p => new
{
    User= p,
    UserNewsLetterMaps = p.UserNewsLetterMaps.Where(c => c.Subscribe == true)
})
.ToList();

Load related entity using eager loading.

_context.Entry(user).Collection(u => u.UserNewsLetterMaps) .Query().Where(news => news.Subscribe) .Select(news => news).Include(news => news.DocTypeRef) .Load();

score 0 · Answer 2 · answered Oct 11 '16 at 19:14

0

You can always create an object that combine the var of both entities, and fill the parts that you would have included conditionnaly.

Personnaly i would make a view in the database that contains all you want to avoid these kind of conditionnals includes (i don't know what your context is exactly) but seriously views work pretty well with entity/linq and everytime i think about trying things like i see here, i work my db instead, and i always gain lots of speed and simplicity (if you don't already have a thousand views).

answered Oct 11 '16 at 19:14

Antoine Pelletier

3,164
3
40
62

I can't create sp or views in the database. right now I'm using below code – Pankaj Rawat Oct 12 '16 at 05:01
//Load only active subscribe news letters _context.Entry(user).Collection(u => u.UserNewsLetterMaps) .Query().Where(news => news.Subscribe) .Select(news => news).Include(news => news.DocTypeRef.NameRef.NameLanguageMaps) .Load(); It working, but looking some good good solution. – Pankaj Rawat Oct 12 '16 at 05:02
What is not working exactly ? You have an error or it's just not doing exactly what you want ? – Antoine Pelletier Oct 12 '16 at 14:18
"So I was trying to achieve this by below code...... but it's not working. " That's what you call an error ? yes it is... I would like to see what is the error – Antoine Pelletier Oct 12 '16 at 14:38
Additional information: 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 – Pankaj Rawat Oct 12 '16 at 14:47
Your includes must be progressives. And i think yours is not exactly. If i had to guess, i'd say it's this part (.Include(u => UserRef.UserNewsLetterExp.Invoke(u)) start by including userNewsLetterExp, if it dosen't change anything... well i'de say you can't use where with include – Antoine Pelletier Oct 12 '16 at 15:13

howt to Include related entities with filter condition using AsExpandable

2 Answers2