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
– man_luck, Commented Jun 4, 2019 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
– S.Amir Nahravan, Commented Jun 4, 2019 at 15:17

Vivien Sonntag · Accepted Answer · 2019-06-04 16:25:26Z

0

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

edited Jun 4, 2019 at 16:25

answered Jun 4, 2019 at 15:40

Vivien Sonntag

4,6591 gold badge29 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

S.Amir Nahravan Over a year ago

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

Vivien Sonntag Over a year ago

I updated my answer regarding your issue. Please check if it helps.

Collectives™ on Stack Overflow

Conditional Include in asp.net core and Entity Framework

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related