CODEWITHSUNDEEP
entity-frameworkfiltering
Id10T-ERROR
Id10T-ERROR

Reputation: 505

Filtering related entities with Entity framework

I'm looking for the best way to to load and filter related child entities. I have something that works, but I'm unsure if it's the best or even the right way to achieve what I want. Working code example below. Pros and cons would be great! Thanks!

public Site Find(int siteID)
{
    // Can't use include here, not possible to filter related (child) entities
    // return _context.Sites.Where(x => x.ID == siteID)
    //                      .Include("SiteLoggers")
    //                      .Where(x => x.Deleted == false)
    //                      .FirstOrDefault();

    var site = _context.Sites.Where(x => x.ID == siteID).FirstOrDefault();

    if(site != null)
    {
        site.SiteLoggers = site.SiteLoggers.Where(x => x.SiteID == siteID && 
                                                       x.Deleted == false)
                                           .ToList();
    }

    return site;
}

EDIT:

Added POCOS

public class Site
{
    public int ID { get; set; }
    public int LocationID { get; set; }
    public bool Active { get; set; }
    public bool Deleted { get; set; }
    public string Name { get; set; }
    public virtual Location Location { get; set; }
    public virtual ICollection<SiteLogger> SiteLoggers { get; set; }
    public virtual ICollection<LinkDcSite> DcSiteLinks { get; set; }
}

public class SiteLogger
{
    public int ID { get; set; }
    public int UID { get; set; }
    public int SiteID { get; set; }
    public int LocationID { get; set; }
    public string Name { get; set; }
    public bool Active { get; set; }
    public bool Deleted { get; set; }
    public virtual Site Site { get; set; }
    public virtual Location Location { get; set; }
    public virtual ICollection<SiteLoggerSensor> SiteLoggerSensors { get; set; }
    public virtual ICollection<LinkLoggerSiteLogger> LinkLoggerSiteLogger { get; set; }
}

Upvotes: 2

Views: 1013

Answers (2)

Saeed Amiri
Saeed Amiri

Reputation: 22555

Your method is fine I think you have just extra checking for x.SiteID == siteID:

....
site.SiteLoggers = site.SiteLoggers.Where(x => !x.Deleted).ToList();
....

Also if you searching by ID means you are sure there is no two element with same ID, so it's better to use SingleOrDefault instead of FirstOrDefault, to throw an exception in the case there are more than one item with one ID.

var site = _context.Sites.Where(x => x.ID == siteID).SingleOrDefault();

Upvotes: 1

JotaBe
JotaBe

Reputation: 39004

You can do that with a simple query:

var site = _context.SiteLoggers.Where(sl => sl.SiteId = siteId && !sl.Deleted).ToList();

If there's a relation between SiteLoggers and Sites, you don't need to chek that the site exists.

Upvotes: 0

Related Questions