Conditional Include in asp.net core and Entity Framework

Question

I just started my first project using asp.net core and for the first time I'm gonna use the code repository for my project in C# and VS 2019. I create a new Model and it called Comment. This Table can save all of the comments on the project, That mean is user comments in POSTS, SOCIALMEDIA, and etc Areas saved in this table

[Key]
public int CommentId { get; set; }

public eTable Table { get; set; }

public int ContentId { get; set; }

[StringLength(1500)]
public string Notes { get; set; }

public virtual AnalysedMarket AnalysedMarket { get; set; }

ContentID is my foreign key, And my eTable enum type is like bellow:

public enum eTable
{
    AnalysedMarket,
    Blog,
    News,
    Migration,
}

I created a new class for AnalysedMarket as well to save Users data from our social media area.

[Key]
public int AnalysedMarketId { get; set; }

[StringLength(255)]
public string Images { get; set; }

public int Hits { get; set; }

public string Notes { get; set; }``

Now I created a method in my code repository for extract data using EF and LINQ to get list of AnalysedMarket data but I can't Include my result with Comment table and result of my code repository in the comment section is null always.

public async Task<IEnumerable<AnalysedMarket>> List(int? page, int? perPage, eStatus? status, string userId)
{
  var query = _db.AnalysedMarkets.Select(a => a);

  if (status.HasValue)
      query = query.Where(m => m.Status.Equals(status));

  if (!string.IsNullOrEmpty(userId))
      query.Where(m => m.CreatedBy.Equals(userId));

  query.Include(a => a.Comments.Where(c => c.Table.Equals(eTable.AnalysedMarket) && c.ContentId == a.AnalysedMarketId));

  if (page.HasValue && perPage.HasValue)
      return await query.OrderBy(a => a.AnalysedMarketId).ToPagedListAsync(page.Value, perPage.Value);
  else
      return await query.OrderBy(a => a.AnalysedMarketId).ToListAsync();
}

Actually my question is how can I get list of AnalysedMarket data included by Comment data. And it has a condition and it says include comment if ContentId is equal to AnalysedMarketId and eTable is Table.AnalysedMarket.

I read the articles about conditional Include but I didn't get any thing of them. Example 1 Example 2

You must load the results from the referenced table as well like _db.Comment.Select(a => whatever the condition is).Include(x => x.AnalysedMarket ) — man_luck, Jun 04 '19 at 15:14
@man_luck There is no way to load data from AnalysedMarket side?? Because I only explained one of the relationships but there are more than several relationships between tables — S.Amir Nahravan, Jun 04 '19 at 15:17

Christoph Sonntag · Accepted Answer · 2019-06-04T16:25:26.110

You need to add a reference from AnalysedMarket to comment like this in your AnalysedMarket-Class:

ICollection<Comment> Comments { get; set; }

And then include them while querying your AnalysedMarkets like this:

var query = _db.AnalysedMarkets.Include(c => c.Comments);

/Edit: Regarding your comment - for this you would need kind of an hierarchy/inheritance structure. It seems to be supported by EfCore and something like this should work:

public class CommentableItem {
    ICollection<Comment> Comments {get;set;}
}

public class Comment {
    CommentableItem CommentableItem {get;set;}
}

public class AnalysedMarket : CommentableItem {
}

Than you should be able to use the include for each item inheriting from CommentableItem. I did not use the inheritance feature yet (as far as I know this is quite new for EF Core), so for further instructions check the documentation

In the usual way that works but the problem is Comment table save other tables comment as well. That mean is comments come from Blog News AnalysedMarket and etc and saved in this table — S.Amir Nahravan, Jun 04 '19 at 16:00
I updated my answer regarding your issue. Please check if it helps. — Christoph Sonntag, Jun 04 '19 at 16:25

Conditional Include in asp.net core and Entity Framework

1 Answers1