Implement a "join" with a variety of tables in Entity Framework

Question

I have three tables:

Materials:

1. ID
2. Title
3. Content

Likes:

1. ID
2. MaterialID
3. UserID
4. IsLiked

Visitors:

1. ID
2. UserID
3. MaterialID
4. Date
5. ReadNow

I would like to get an object like:

1. Title
2. Content
3. CountLikes
4. CountVisitors

I tried to do the following:

from mat in ctx.materials
let visitors = mat.VisitorsCollection.Where(x=>x.ReadNow).Count()
let likes = mat.LikesCollection.Where(x=>x.IsLiked).Count()
let iliked = mat.LikesCollection.Where(x=>x.UserID == myID && x.IsLiked).Any()
select new {
   Material = mat,
   Visitors = visitors,
   Likes = likes,
   Liked = iliked
}

I get a selection of materials and separately the Entity Framework receives data on the number of visitors and so on.

I also tried the following:

from mat in ctx.materials
join lik in ctx.Likes.Where(x=>x.UserID == myID && x.IsLiked) on map.ID equals lik.MaterialID 
select new {
   Material = mat,
   Liked = lik.Any()
}

but now an error occurs:

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Any()' could not be translated and will be evaluated locally.

Answer 1

Well if you have foreign keys in the database then the EF would generate links between the objects so all you need to do is:

var result = ctx.materials.Select(x => 
     new SomeClass{
          Material = x,
          Visitors = x.Visitors.Where(v => v.ReadNow).Count(),
          Likes = x.Likes.Where(y => y.IsLiked).Count(),
          Liked = x.Likes.Where(z => z.IsLiked && z.UserID == myID).Count()
     }).ToList();

The syntax maybe is not totally correct, but you get the point ...

Answer 2

If you are using entity framework, consider to use the ICollections, instead of performing the joins yourself.

You have a sequence of Materials where every Material has zero or more Likes and zero or more Visitors, both one-to-many relations, using a foreign key to Material.

If you've followed the entity framework code first conventions, you'll have classes similar to the following

class Material
{
     public int Id {get; set;}
     public string Title {get; set;}
     public string Content {get; set;}

     // every Material has zero or more Likes (one-to-many)
     public virtual ICollection<Like> Likes {get; set;}

     // every Material has zero or more Visitors (one-to-many)
     public virtual ICollection<Visitor> Visitors {get; set;}
}

Likes and Visitors:

class Like
{
     public int Id {get; set;}
     public bool IsLiked {get; set;}
     ...

     // every Like belongs to exactly one Material, using foreign key
     public int MaterialId {get; set;}
     public virtual Material Material {get; set;}
}

class Visitor
{
     public int Id {get; set;}
     ...

     // every Visitor belongs to exactly one Material, using foreign key
     public int MaterialId {get; set;}
     public virtual Material Material {get; set;}
}

This is all that entity framework needs to detect the one-to-many relationships. It might be that you want different table names, or different identifiers for your columns. In that case attributes or fluent API is needed

In entity framework the columns of the tables are represented by non-virtual properties. The virtual properties represent the relations between the tables (one-to-many, many-to-many, etc)

Once you've got your class definitions correctly, your query is simple and very intuitive:

Requirement:

From my collection of Materials, give me from every Material, the Title, the Content, the number of Likes it has and the number of Visitors it has:

var result = myDbContext.Materials
   .Where(material => ...)            // only if you don't want all Materials
   .Select(material => new            // from every Material make one new object
   {                                  // containing the following properties
       Title = material.Title,
       Content = material.Content,

       // if you want any information of the likes of this material, use property Likes
       LikeCount = material.Likes
           .Where(like => like.IsLiked)  // optional, only if you don't want all likes
           .Count(),
       NrOfVisitors = material.Visitors
           .Where(visitor => ...)        // only if you don't want all visitors
           .Count(),
   });

In words: from my complete collection of Materials, keep only those Materials that ... From every remaining Material, make one new object:

• Title is the title of the Material
• Content is the content of the Material
• LikeCount is the number of Likes of this material (that have a true IsLiked)
• NrOfVisitors is the number of Visitors of this material (that are ...)

Entity framework knows your relations, and knows that a GroupJoin is needed.