CODEWITHSUNDEEP
c#linqef-code-first
J86
J86

Reputation: 15237

LINQ Include condition not applying

I have the following two entities:

public class Artist
{
    public int Id { get; set; }
    public string FName { get; set; }
    public string LName { get; set; }
    public string UrlFriendly { get; set; }
    public string ImgURL { get; set; }
    public bool Verified { get; set; }
    // relations
    public virtual ICollection<Painting> Paintings { get; set; } 
}

And:

public class Painting
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string ImgUrl { get; set; }
    public bool Verified { get; set; }
    // relations
    public int ArtistId { get; set; }
    public virtual Artist Artist { get; set; }
}

Then in my data access layer, I have the following LINQ:

public Artist GetArtistByUrlFriendly(string urlFriendly)
{
    return _context
            .Artists
            .Include("Paintings")
            .Where(a => a.Verified == true && a.Paintings.Any(p => p.Verified == true))
            .FirstOrDefault(a => a.UrlFriendly == urlFriendly);
}

So I want a particular artist and his paintings but this artist must be verified and his paintings too must be verified. That above LINQ should do it no?

But it doesn't! It returns paintings that aren't verified too! Any idea why this can be?

Upvotes: 0

Views: 54

Answers (1)

Douglas
Douglas

Reputation: 54887

Your query only checks that the artist has at least one verified painting. If they do, then the Include("Paintings") will load all their paintings (verified or not).

Do you mean that you only want to return the verified paintings of the given artist? In that case, you could issue a separate query to populate just their verified paintings.

public Artist GetArtistByUrlFriendly(string urlFriendly)
{
    var artist = _context.Artists.FirstOrDefault(a => 
        a.UrlFriendly == urlFriendly && a.Verified);

    if (artist != null)
        artist.Paintings = _context.Paintings.Where(p => 
            p.ArtistId == a.Id && p.Verified).ToList();

    return artist;
}

Edit: In case the Artist.Paintings property is read-only, you can use the following call instead (adapted from this answer and this blog post):

    if (artist != null)
        context.Entry(artist)
               .Collection(a => a.Paintings)
               .Query()
               .Where(p => p.Verified)
               .Load();

Upvotes: 1

Related Questions