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?
Accepted Answer
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();
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
};
}
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow