how to include a column from a second table in my LINQ query

Question

how to include a column from a second table in my LINQ query

c# entity-framework entity-framework-6 linq-to-sql

Question

I have a table called Ad (advertisement):

public class Ad
{
    public long AdId { get; set; }
    public long UserId { get; set; } // <-- this is advertiser not currentUser
    public string Description { get; set; }
}

Users can add zero or more advertisements to their favourites list, so I have created another table called Favourite:

public class Favourite
{
    public long FavouriteId { get; set; }
    public long AdId { get; set; }
    public long UserId { get; set; }
}

And I have a table called User:

public class User
{
    public long UserId { get; set; }
    public string Name { get; set; }
}

This is how I retrieve an ad, given currentUserId and adId:

public TEntity GetAd(long currentUser, long adId)
{
    return Context.Ad
        .Where(r.AdId == adId)
        // include a flag indicating the pair (adId and currentUserId) exist in Favouries table
        .FirstOrDefault();
}

What I want to include in the result is a flag, indicating if the ad is added to the favourites list or not?

Hooman Bahreini

Popular Answer

public object GetAd(long currentUser, long adId)
{
    return from a in Context.Favourite
           where a.UserId  == currentUser
           select new {
                      flag = a.AdId != null
                      a.FavouriteId,
                      //etc
                      };
}

CodeMan

View more on Stack Overflow

Ehsan Sajjad · Accepted Answer · 1/13/2019 6:47:11 AM

~~In Entity Framework we have navigation properties. You can define navigation property on child table for the parent table row. For you case you can change your child entity like following:~~

public class Favourite { public long FavouriteId { get; set; } public long AdId { get; set; } public long UserId { get; set; } public virtual Ad Ad { get; set; } }

and now the Entity Framework should take care of populating it for you and you can access the Ad related row of Favourite like :

return Context.Ad .Where(r => r.UserId == userId && r.AdId == adId) .Select(x => x.Ad.Description);

But in your case, You can write a query to know if the ad is favourite or not:

return Context.Ad
        .Where(r.AdId == adId)
        Select(x => new 
                   { 
                      ad = x, 
                      IsFavourite = Context.Favourite.Any(y=> y.AdId = adId 
                                                    && y.UserId = currentUserid))
        .FirstOrDefault();

you can create a Domain View Model and map it to that :

public UserFavouriteAd
{
    public Ad Ad { get; set; }
    public IsFavourite { get; set; }
}

and populate it:

return Context.Ad
            .Where(r.AdId == adId)
            .ToList()
            Select(x => new UserFavouriteAd 
                       { 
                          ad = x, 
                          IsFavourite = Context.Favourite.Any(y=> y.AdId = adId 
                                                        && y.UserId = currentUserid))
            .FirstOrDefault();

how to include a column from a second table in my LINQ query

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related