Access count of related records of an entity Entity Framework

Question

I have two models:

public class HouseType
 {
    public int Id { get; set; }
    public string TypeName { get; set; }

    public virtual IEnumerable<HouseModel> HouseModels { get; set; }
 }

and

public class HouseModel
{
    public int Id { get; set; }
    public string ModelName { get; set; }

    [DisplayFormat(DataFormatString = "{0:n2}")]
    public double StandardPrice { get; set; }

    [ForeignKey("HouseType")]
    public int HouseTypeID { get; set; }

    public virtual HouseType HouseType { get; set; }

    public virtual IEnumerable<HouseUnit> HouseUnits { get; set; }
}

I am returning a JSON result, so as expected I cannot manipulate it in a view, because the display is handled by a javascript file that I made.

I am trying to retrieve the number of HouseModel that is contained by HouseType. I have tried:

db.HouseTypes.Select(h => new
 {
     HouseCount = h.HouseModels.Count()
  }).ToList();

But Entity Framework complains about it. How can I access the count of related records inside an entity? Any help will be much appreciated. Thanks.

What error are you getting? (you probably need `db.HouseTypes.Include("HouseModels").Select(....` — , Jan 13 '16 at 06:10
It says `specified type member not supported linq...` @StephenMuecke — Cyval, Jan 13 '16 at 06:11
@FrebinFrancis Post that one as an answer. Worked like a charm. Than ks. — Cyval, Jan 15 '16 at 01:48

score 1 · Accepted Answer · answered Jan 15 '16 at 04:37

1

Use

public virtual ICollection<HouseUnit> HouseUnits { get; set; }

instead of

public virtual IEnumerable<HouseUnit> HouseUnits { get; set; }

Hope this helps.

answered Jan 15 '16 at 04:37

Frebin Francis

1,905
1
11
19

Thanks! Can you please explain why ? – Cyval Jan 15 '16 at 05:12
@Cyval Better check this link http://stackoverflow.com/questions/10113244/why-use-icollection-and-not-ienumerable-or-listt-on-many-many-one-many-relatio – Frebin Francis Jan 15 '16 at 05:32

score 0 · Answer 2 · answered Jan 13 '16 at 19:14

Simply speaking, the trouble is that EF is trying to execute the .Select() statement on the db server but, of course, the db server does not know how to create a new object.

You first need to bring back the counts then create your objects so something like this should work better:

var listOfCounts = db.HouseTypes
    .Select(h => h.HouseModels.Count())
    .ToList()   
    .Select(c => new
    {
         HouseCount = c
    })
    .ToList();

in this example when the first .ToList() is executed the db needs only return a set of numbers (the counts of HouseModels in each HouseType) then we have a List<int> in local memory from which we can create our objects with the second Select statement.

As an aside...

It wasn't part of your original question but maybe you'd want to consider a dictionary rather than a list so you have some means of identifying which count of HouseModels belonged to each HouseType? in which case we could do something like:

Dictionary<int,string> houseModelCounts = db.HouseTypes
    .ToDictionary(h => h.Id, h => h.HouseModels.Count());

which would give a dictionary keyed with the HouseType Id with values for the count of HouseModels in each type. I don't know your context though so maybe unnecessary for you?

Access count of related records of an entity Entity Framework

2 Answers2