Entity Framework: generate dynamic where clause and select custom columns

Question

I have 3 tables: Person, PersonFriend, PersonGroup.

Using LINQ, i want to join the 3 tables, filter using a dynamically generated where clause, and select custom columns with flattened rows (flattened one-to-many relationship table columns).

Pseudo-SQL design:

CREATE TABLE Person (int id, varchar socialclass, date createddate);
CREATE TABLE Person_Friend (int id, id personid references person.id, id friendpersonid references person.id, varchar friendtype);
CREATE TABLE Person_Group (int id, int memberid references person.id, varchar membershiplevel);

Entities:

public class Person
{
    public int Id { get; set; }

    public string SocialClass { get; set; }

    public DateTime? CreatedDate { get; set; }

    public ICollection<PersonFriend> Friend { get; set; }

    public ICollection<PersonGroup> Group { get; set; }
}

public class PersonFriend
{
    public int Id { get; set; }

    public int PersonId { get; set; }

    public int FriendPersonId { get; set; }

    public string FriendType { get; set; }
}

public class PersonGroup
{
    public int Id { get; set; }

    public int MemberId { get; set; }

    public string MembershipLevel { get; set; }
}

query syntax LINQ:

var queryResult = from person in _context.Person
                join friend in _context.PersonFriend on person.Id equals friend.FriendPersonId
                join group in _context.PersonGroup on person.Id equals group.MemberId
                where (friend.PersonId == 1 && friend.FriendType == "type1") || (friend.PersonId == 3 && friend.FriendType == "type2") || ...
                select new { person.Id, person.SocialClass, person.CreatedDate, friend.FriendPersonId, friend.FriendType, group.Id, group.MembershipLevel };

Notice the where clause; Given a list of { PersonId, FriendType } object, I want to build the where clause like above.

Since I could not figure building a dynamic where clause for a query syntax LINQ, I tried converting it to the Method syntax LINQ statement so i can leverage the PredicateBuilder (http://www.albahari.com/nutshell/predicatebuilder.aspx) but I run into the problem during Selecting one-to-many things into a flattened object.

var methodResult = _context.Person
                .Include(x => x.Friend)
                .Include(x => x.Group)
                .Select(person => new { person.Id, person.SocialClass, person.CreatedDate, person.friend.FriendPersonId, person.friend.FriendType, person.group.Id, person.group.MembershipLevel }); 

notice that the above Select is not possible because friend is a ICollection.

I also tried using the above query syntax LINQ statement without the where clause, making it return a object instead of an annonymous object, and then calling the method .Where() with the predicate builder. But the built expression runs into LINQ => Entity Framework SQL conversion error and executes the where in the application, not in DB.

var queryResultWithoutWhere = from person in _context.Person
                join friend in _context.PersonFriend on person.Id equals friend.FriendPersonId
                join group in _context.PersonGroup on person.Id equals group.MemberId
                select new SelectedObject { PersonId = person.Id, SocialClass = person.SocialClass, CreatedDate = person.CreatedDate, FriendId = friend.FriendPersonId, FriendType = friend.FriendType, GroupId = group.Id, MembershipLevel = group.MembershipLevel };


var predicate = PredicateBuilder.New<SelectedObject>(false);

foreach (var searchObject in searchRequestObjects)
{
    predicate.Or(p => p.FriendPersonId == searchObject.FriendPersonId && p.FriendType == searchObject.FriendType);
}

var result = queryResultWithoutWhere.Where(predicate).ToList();

I feel like I tried everything I could, and I cannot seem to generate this SQL. Last resort would be writing a raw SQL string and then executing it, but I really would like to get this working with Entity Framework.

How would I accomplish creating a dynamic where clause, select into a custom flattened object, and have entity framework generate the SQL?

Answer 1

You can use SelectMany to flatten the collections:

var methodResult = Persons
                .Include(x => x.Friend)
                .Include(x => x.Group)
                .SelectMany(person =>
                    person.Friend.SelectMany(friend =>
                        person.Group.Select(group =>
                            new {
                                person.Id,
                                person.SocialClass,
                                person.CreatedDate,
                                friend.FriendPersonId,
                                friend.FriendType,
                                GroupId = group.Id,
                                group.MembershipLevel
                            }
                        )
                    )
                );