CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworklinq
Cyval
Cyval

Reputation: 2519

Access count of related records of an entity Entity Framework

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.

Upvotes: 1

Views: 161

Answers (2)

Frebin Francis
Frebin Francis

Reputation: 1915

Use 

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

instead of 

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

Hope this helps.

Upvotes: 1

Stewart_R
Stewart_R

Reputation: 14515

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?

Upvotes: 0

Related Questions