How to use Linq or Lambda to join 1-to-many tables and project flattened results into an anonymous type

Question

I have 3 tables linked by Foreign Keys: ChangeSet, ObjectChanges, PropertyChanges. These tables have 1-To-Many Relationships with each other. I need to join and project and flatten the results into an anonymous type.

We use Entity Framework at the data layer and I essentially need to make the following Query with linq.

select c.Id as ChangeSetId,
c.Timestamp,
c.Author_Id,
u.Name as [User],
o.id as ObjectChangeId,
o.TypeName,
o.ObjectReference as EntityId,
o.DisplayName,
p.Id as PropertyChangeId,
p.PropertyName, 
p.ChangeType,
p.OriginalValue, 
p.Value  
from ChangeSets c
inner join ObjectChanges o
    on c.Id = o.ChangeSetId
left join PropertyChanges p
    on p.ObjectChangeId = o.Id
inner join Users u
    on u.Id = c.Author_Id
order by c.id desc

The Method in question however looks like this:

GetAllWhereExpression(Expression<Func<ChangeSet, bool>> expression)

The expression in this case is likely to be a Where o.EntityId = [Some Value] and c.TimeStamp > X and < Y.

I got very close I felt in linq with the following but couldn't figure out how to inject the expression: (The .GetRepository().Entities is basically DbSet)

var foo = from c in _uow.GetRepository<ChangeSet>().Entities
        join o in _uow.GetRepository<ObjectChange>().Entities on c.Id equals o.ChangeSetId
        join p in _uow.GetRepository<PropertyChange>().Entities on o.Id equals p.ObjectChangeId
        where expression // This Line Not Valid
        select new
        {
            ChangeSetId = c.Id,
            Timestamp = c.Timestamp,
            User = c.User.DisplayName,  
            EntityType = o.TypeName,
            EntityValue = o.DisplayName,
            Property = p.PropertyName,
            OldValue = p.OriginalValue,
            NewValue = p.Value
        };

I'd prefer to use Lambda syntax but I can't figure out how to construct it. I know I need SelectMany to project and flatten the results but I can't figure out how to use them within the anonymous type for the subcollections:

var queryable = _uow.GetRepository<ChangeSet>().Entities // This is basically the DbSet<ChangeSet>()
            .Where(expression)
            .SelectMany(c => new
            {
                ChangeSetId = c.Id,
                Timestamp = c.Timestamp,
                User = c.User.DisplayName,  
                EntityType = c.ObjectChanges.SelectMany(o => o.TypeName), //Doesn't work, turns string into char array
                //PropertyName = c. this would be a 1 to many on the entity
            }
                )

How do I craft the linq to produce basically the same results as the sql query?

Answer 1

Here is how it may look like in method syntax.

_uow.GetRepository<ChangeSet>().Entities
    .Where(expression)
.Join(_uow.GetRepository<ObjectChanges>().Entities, cs => cs.Id, oc => oc.ChangeSetId, 
    (cs, oc) => new { cs, oc })
.Join(_uow.GetRepository<PropertyChanges>().Entities, outer => outer.oc.Id, pc => pc.ObjectChangeId, 
    (outer, pc) => new { cs = outer.cs, oc = outer.cs, pc })
.Join(_uow.GetRepository<User>().Entities, outer => outer.cs.Author_Id, u => u.Id, 
    (outer, u) => new { 
        ChangeSetId = outer.cs.Id,
        Timestamp = outer.cs.Timestamp,
        User = u.DisplayName,  
        EntityType = outer.oc.TypeName,
        EntityValue = outer.oc.DisplayName,
        Property = outer.pc.PropertyName,
        OldValue = outer.pc.OriginalValue,
        NewValue = outer.pc.Value
    })

Answer 2

Please follow the given demonstration, Four entities joined by LINQ extension methods.
N.B: .Join use for Inner Join

var foo=_uow.GetRepository<ChangeSet>().Entities
        .Join(_uow.GetRepository<ObjectChange>().Entities,
        c=>c.Id,
        o=>o.ChangeSetId,
        (c,o)=>new{ChangeSet=c,ObjectChange=o})

        .Join(_uow.GetRepository<PropertyChange>().Entities,
        o=>o.ObjectChange.Id,
        p=>p.ObjectChangeId,
        (o,p)=>new{o.ChangeSet,o.ObjectChange,PropertyChange=p})

        .Join(_uow.GetRepository<Users>().Entities,
        c=>c.ChangeSet.Author_Id,
        u=>u.Id,
        (c,u)=>new{c.ChangeSet,c.ObjectChange,c.PropertyChange,User=u})

        .Select(x=>new
        {
             ChangeSetId=x.ChangeSet.Id,
             x.ChangeSet.Timestamp,
             x.ChangeSet.Author_Id,
             User=x.User.Name,
             ObjectChangeId=x.ObjectChange.id,
             x.ObjectChange.TypeName,
             EntityId=x.ObjectChange.ObjectReference,
             x.ObjectChange.DisplayName,
             PropertyChangeId=x.PropertyChange.Id,
             x.PropertyChange.PropertyName, 
             x.PropertyChange.ChangeType,
             x.PropertyChange.OriginalValue, 
             x.PropertyChange.Value
        }).OrderByDescending(x=>x.ChangeSetId).ToList() //ChangeSetId=c.Id

Answer 3

To keep it simple imagine each table has a column named Description and we want to retrieve a flat result like this:

-------------------------------------------------------------------------------------
ChangeSetDescription  |   ObjectChangeDescription   |    PropertyChangeDescription  |
-------------------------------------------------------------------------------------

---

Short Answer

You do not need to do a join because EF will figure out the joining for you based on your data model. You can just do this. Imagine ctx is an instance of a class which derives DbContext :

var query = ctx.ChangeSets
    .SelectMany(x => x.ObjectChanges, (a, oc) => new
    {
        ChangeSetDescription = a.Description,
        ObjectChangeDescription = oc.Description
    ,
        Pcs = oc.PropertyChanges
    })
    .SelectMany(x => x.Pcs, (a, pc) => new
    {
        ChangeSetDescription = a.ChangeSetDescription,
        ObjectChangeDescription = a.ObjectChangeDescription,
        PropertyChangeDesription = pc.Description
    });
var result = query.ToList();

---

Long Answer

Why do I not need the join?

When you create your EF model, regardless of whether you are using code first, database first or a hybrid approach, if you create the relationships correctly then EF will figure out the joins for you. In a database query, we need to tell the database engine how to join, but in EF we do not need to do this. EF will figure out the relationship based on your model and do the joins using the navigation properties. The only time we should be using joins is if we are joining on some arbitrary property. In other words, we should seldom use joins in EF queries.

Your EF queries should not be written as a direct translation of SQL queries. Take advantage of EF and let it do the work for you.

Can you explain the query in the short answer?

It is the SelectMany method which will throw readers off a little. But it is pretty easy. Let's use a simple class as example:

public class Student
{
    public string Name { get; private set; }
    public IEnumerable<string> Courses { get; private set; }
    public Student(string name, params string[] courses)
    {
        this.Name = name;
        this.Courses = courses;
    }
}

Let's create 2 students and add them to a generic list:

var s1 = new Student("George", "Math");
var s2 = new Student("Jerry", "English", "History");
var students = new List<Student> { s1, s2 };

Now let's imagine we need to get the name of the students and the classes they are taking as flat result set. In other words, we do not want Jerry to have a list with 2 subjects but instead we want 2 records for Jerry. Naturally, we will think of SelectMany and do this:

var courses = students.SelectMany(x => new { Name = x.Name, Courses = x.Courses });

But that will not work and cause a compilation error:

The type arguments for method 'Enumerable.SelectMany<TSource, TResult> 
(IEnumerable<TSource>, Func<TSource, IEnumerable<TResult>>)'
cannot be inferred >from the usage. Try specifying the type arguments explicitly.

The SelectMany method takes a Func which will return IEnumerable<TResult> NOT TResult so that will not work. Luckily, it has an overload that we can use. But the overload has a, well, intimidating signature:

public static IEnumerable<TResult> SelectMany<TSource, TCollection, TResult>
(
    this IEnumerable<TSource> source, 
    Func<TSource, IEnumerable<TCollection>> collectionSelector,
    Func<TSource, TCollection, TResult> resultSelector);

Basically what that is saying is the first Func should return an IEnumerable<TCollection> and the second will receive the TSource and TCollection as input and we can return a TResult as output. Ok so let's do that:

var courses = students.SelectMany(x => x.Courses, 
    (a, b) => new { Name = a.Name, Course = b });

So in the above line, a is the source which is our students list and b is the string yielded to us one by one from the first Func which we said will return x.Courses. The TResult we are returning is an anonymous type.

Hopefully this example will clarify what is going on in the short answer.

Answer 4

Or what you can do is, since your where expression is for changeSet table, you can filter it when you select from it within your linq

  var foo = from c in _uow.GetRepository<ChangeSet>().Entities.Where(expression) //Notice lambda exp here
    join o in _uow.GetRepository<ObjectChange>().Entities on c.Id equals o.ChangeSetId
    join p in _uow.GetRepository<PropertyChange>().Entities on o.Id equals p.ObjectChangeId
   select new
    {
        ChangeSetId = c.Id,
        Timestamp = c.Timestamp,
        User = c.User.DisplayName,  
        EntityType = o.TypeName,
        EntityValue = o.DisplayName,
        Property = p.PropertyName,
        OldValue = p.OriginalValue,
        NewValue = p.Value
    };