3.1.11.0706 Final (SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll) 3.1.12.0806 (SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll 3.1.12.0507 (SD.LLBLGen.Pro.LinqSupportClasses.NET35) DotNet 4.0 vs2010 project Adapter template

I have an entity collection with prefetch path that defined lots of criteria. I want to project it into custom classes that contain counts. When I do this without getting the collection as an IEnumerable or List then the counts ignore all the criteria. If I first do ToList() the counts then work.

Here is the (simplified) code the get the count(s):

            var facilityRegionEntities = model.RegulationCountryRegions<FacilityEntity>(438, "cm");
            var countryRegions = from a in facilityRegionEntities.ToList()
                                 select new Dashboard
                                            {
                                                FacilityCode = a.FacilityCode,
                                                CountryCode = a.CountryCode,
                                                RegionCode = a.RegionCode,
                                                TotalRegulations =
                                                    a.RegionBase.RegulationCountryRegionIncludeFederal.Distinct().Count(),
                                            };

and here is the (simplified) Linq for the criteria:

        public IQueryable<FacilityEntity> RegulationCountryRegions<T>(int idClient, string serviceCode)
        {
            var facilityRegionEntities = (from regions in metaData.Facility
                                                   where regions.IDClient == idClient 
                                                       && regions.ServiceCode.Equals(serviceCode, StringComparison.CurrentCultureIgnoreCase)
                                                   select regions)
                .WithPath(regions => regions.Prefetch<RegionBaseEntity>(y => y.RegionBase)
                    .FilterOn(y3 => y3.ClientRegion.Any( b => b.IDClient == idClient && b.ServiceCode.Equals(serviceCode, StringComparison.CurrentCultureIgnoreCase)))
                    .SubPath(y1=>y1.Prefetch<RegulationCountryRegionIncludeFederalEntity>(y2=>y2.RegulationCountryRegionIncludeFederal)

                        .FilterOn(y2=>y2.RegulationBase.Archived == false))
                                    
               );
            return facilityRegionEntities;
        }

If I remove the .ToList() then the criteria is ignored but the performance is terrible (timesout after 30 seconds)

How do I construct a collection so that counts are carried out on the database? I can manually modify the generated sql to add counts, but I don't quite manage to create the Linq to do it itself.

Try CountColumn() on the primary key and set distinct to true.

Walaa wrote:

Try CountColumn() on the primary key and set distinct to true.

I tried:

TotalRegulations = b.RegionBase.RegulationCountryRegionIncludeFederal.CountColumn(d=>d.RegID,true),

on the first code section above but it wouldn't compile. It looks like I need to explicitly reference a dll?

Error   2   'Enhesa.BusinessObjects.Adapter.HelperClasses.EntityCollection< Enhesa.BusinessObjects.Adapter.EntityClasses.RegulationCountryRegionIncludeFederalEntity>' does not contain a definition for 'CountColumn' and no extension method 'CountColumn' accepting a first argument of type 'Enhesa.BusinessObjects.Adapter.HelperClasses.EntityCollection< Enhesa.BusinessObjects.Adapter.EntityClasses.RegulationCountryRegionIncludeFederalEntity>' could be found (are you missing a using directive or an assembly reference?)

I have searched the documentation, the forum and the www for CountColumn but can't find a sample piece of code to see how the syntax works. Do you know of one I can look at?

The CountColumn is an LINQ2LLBL extension method for IQueryables. It could be used like: var orders = new List<OrderEntity>();

using (var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter);
    orders = (from o in metaData.Order
                select new OrderEntity
                {
                    OrderId = o.OrderId,
                    CustomerId = o.CustomerId,
                    EmployeeId = o.EmployeeId,
                    TotalProductDetails = metaData.OrderDetail.Where(od => od.OrderId == o.OrderId).CountColumn(od => od.ProductId, true)
                }).ToList();
}

or

var q = from d in metaData.Department
        join e in metaData.Employee on d.DepartmentId equals e.WorksForDepartmentId
        select d;
int count = q.CountColumn(d => d.DepartmentId, true);

count = metaData.Department.Where(d=>d.Employees.Count() > 0).CountColumn(d => d.DepartmentId, true);

It's mentioned in the docs and it's used in many code examples in the shipped LINQ2LLBL snippets at [LLBLGen installation Folder]\Frameworks\LLBLGen Pro\ExampleQueries\Linq to LLBLGen Pro