CODEWITHSUNDEEP
c#entity-frameworklinq
Erre Efe
Erre Efe

Reputation: 15577

Get parent object plus its child count without anonymous type

So I have the next method (which works) to return a list of claims plus its observations. One claim can have zero-or-many observations. Code works but I'm afraid its a mess, with the anonymous type and then parsing it into a new Claim type, setting the count. 

public async Task<IEnumerable<Claim>> GetClaims(ClaimStatusCode status, int take = 10, int skip = 0)
{
    using (var db = new DataContext())
    {    
        var pendingclaims = await (from claim in db.Claims
                                where claim.OfficeCode == _officeCode
                                where claim.ClaimStatusCode == status
                                select new
                                {
                                    ID = claim.ID,
                                    ClaimStatusCode = claim.ClaimStatusCode,
                                    OpenDate = claim.OpenDate,
                                    LastUpdateDate = claim.LastUpdateDate,
                                    CloseDate = claim.CloseDate,
                                    ProductCode = claim.ProductCode,
                                    IssueCode = claim.IssueCode,
                                    SpecificIssueCode = claim.SpecificIssueCode,
                                    OfficeCode = claim.OfficeCode,
                                    Summary = claim.Summary,
                                    ObservationsCount = claim.Observations.Count
                                }).OrderBy(c => c.OpenDate).Take(take).Skip(skip).ToListAsync();                     

        var list = new List<Claim>();
        foreach (var claim in pendingclaims)
        {
            Claim c = new Claim()
            {
                ID = claim.ID,
                ClaimStatusCode = claim.ClaimStatusCode,
                OpenDate = claim.OpenDate,
                LastUpdateDate = claim.LastUpdateDate,
                CloseDate = claim.CloseDate,
                ProductCode = claim.ProductCode,
                IssueCode = claim.IssueCode,
                SpecificIssueCode = claim.SpecificIssueCode,
                OfficeCode = claim.OfficeCode,
                Summary = claim.Summary,
                ObservationsCount = claim.ObservationsCount
            };
            list.Add(c);
        }

        return list;
    }
}

I think maybe I'm missing something to reduce the mess of the resulting SQL query, but don't figure what. Any idea?

UPDATE

As requested, here's the Claim and Observation class, I'm using a plain simple Entity Code First One to Many relationship:

Claim

public class Claim
{
    public Claim()
    {
        Observations = new List<Observation>();
    }

    [Key]
    public Guid ID { get; set; }

    ...

    public virtual ICollection<Observation> Observations { get; set; }

    [NotMapped]
    public int ObservationsCount { get; set; }
}

Observation

public class Observation
{
    public Observation()
    { }

    [Key]
    public Guid ID { get; set; }

    ...

    public virtual Guid ClaimID { get; set; }

    [ForeignKey("ClaimID")]
    public virtual Claim Claim { get; set; }
}

Upvotes: 1

Views: 123

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205919

There is no way in EF6 to get what you want without some intermediate projection (being it anonymous type or concrete type, as soon as it's not an entity type). But if you need all the object fields plus child count, you can simplify the implementation like this:

var pendingclaims = await (from claim in db.Claims.AsNoTracking()
                           where claim.OfficeCode == _officeCode
                           where claim.ClaimStatusCode == status
                           orderby claim.OpenDate
                           select new
                           {
                               claim,
                               ObservationsCount = claim.Observations.Count
                           }).Take(take).Skip(skip).ToListAsync();                     

return pendingclaims.Select(item =>
{
    item.claim.ObservationsCount = item.ObservationsCount;
    return item.claim;
}).ToList();

Upvotes: 3

Related Questions