Is it possible to get the total number of records returned when using .Skip().Take() paging? If you want to write out a list of page links (e.g. (previous | 1 | 2 | 3 | next) you need to know the total number of records to be able to calculate the number of page links.

You can do this: (example) var q = from c in metaData.Customers where c.Country == "USA" select c;

int count = q.Count();

var page = q.TakePage(x, y);

or

var page = q.Skip(n).Take(m);

i.o.w.: first create the base query, then create two new expression trees from that first one.

Thanks Otis,

What I had was this:

     CompanyCollection ret;
            ILLBLGenProQuery q = (ILLBLGenProQuery)(
                  from c in _metaData.Company
                  join o in _metaData.CompanyStateServiced on c.CompanyId equals o.CompanyId
                  where o.AddressStateId == stateId
                  orderby c.CreatedDateTimeUtc descending
                  select c).Skip((page - 1) * pageSize).Take(pageSize);

            ret = (CompanyCollection)q.Execute();
return ret;

And I’ve changed it to this:

CompanyCollection ret;

                var q = from c in _metaData.Company
                  join o in _metaData.CompanyStateServiced on c.CompanyId equals o.CompanyId
                  where o.AddressStateId == stateId
                  orderby c.CreatedDateTimeUtc descending
                  select c;

            int count = q.Count();
            
            //Do something with count
            
            ret = new CompanyCollection((q.Skip((page - 1) * pageSize).Take(pageSize).ToList<CompanyEntity>)());

            LogCompanyView(ret);
            return ret;

Which worked a treat and gave me the total count I needed.

I’m still getting my head around all this Linq stuff, can you explain the purpose of the ILLBLGenProQuery type object please?

The 'var' keyword is used here because you can't always know the type returned by the query. Or it's not important for example.

Say we have this query: var q = from c in metaData.Customers where c.Country=="USA" select c;

very simple. Now, when I do: foreach(var v in q) { // do something }

I enumerate over it. This internally calls GetEnumerator() and that calls the execute method on the object referred to by q. Which simply does an entity collection fetch and as the enumerator is required, the enumerator of the entity collection is returned.

However, if I do: List<CustomerEntity> customers = q.ToList();

the query is also executed, but it's less efficient: the query is executed internally, and enumerated and copied into a new List<CustomerEntity>.

So if you just want to have the EntityCollection<CustomerEntity> (or for selfservicing, CustomerCollection), you are out of luck. So we introduced the interface. You can cast the query to ILLBLGenProQuery and then you can call Execute. This simply executes the query and returns the result. This has the advantage that you get the result in an entitycollection with which you can create views in memory and have other fancy features

Linq's design is a bit crippled. So you have two types of queries: queries which execute directly, which are queries which return a scalar value, e.g. the count, and you have queries which are only executed when you enumerate them. That second group is in our case always returned as an LLBLGenProQuery<T>. This is not really useful unless you know what 'T' is. For entities this is doable, in the query above, T is CustomerEntity. However in the case of anonymous types, this isn't possible, as these types are created by the compiler.

So to access the features of LLBLGenProQuery<T>, you need an interface without the generic argument. Hence 'ILLBLGenProQuery'

Your second query is less efficient, as you copy the data over to a new collection. What you can do is this:

CompanyCollection ret;

                var q = from c in _metaData.Company
                 join o in _metaData.CompanyStateServiced on c.CompanyId equals o.CompanyId
                 where o.AddressStateId == stateId
                 orderby c.CreatedDateTimeUtc descending
                 select c;

            int count = q.Count();
            
            //Do something with count
            
            ret = ((ILLBLGenProQuery)q).Execute<CompanyCollection>();
            LogCompanyView(ret);
            return ret;

As 'q' is of type LLBLGenProQuery<T>. q.Count(); creates a NEW expression tree, where q is a constant. It's evaluated but all expressions are handled into NEW expressions, so 'q' is always left untouched.

Much appreciated Otis, thanks for shedding a little more light and the tip on improving the performance of my new query

I have one to many relation Person - Car

And query as below:

        
var query = (from p in metaData.Person
                 join c in metaData.Car on p.IdPerson equals c.IdPerson
                 where c.Producer == "VW"               
                 select p);

var queryForPaging = query.Skip(startRowIndex)
        .Take(maximumRows)
        .WithPath(pref => pref.Prefetch(p => p.Car));

List<PersonEntity> personList = q.ToList<PersonEntity>();

PersonList contains unique persons who have VW cars with references to their cars.

I would like to know how many uniqe persons returns query using the same query. When I use code as below I have all records which query produces and the count > uniqe person count

int count = query.Count<PersonEntity>();

I need it for paging.

I think you may need to count distinct Persons, as the resultset of a Join might include more records than those in one table.

Docs wrote:

CountColumn(field [, bool applyDistinct]). This method can count on a field and if true is passed for applyDistinct, the count will be a Count distinct. Currently Count() is a CountRow (which is a Count(*))

Walaa, thank you for reply.

I tried to use CountColumn method :

int count = query.CountColumn(x => x.IdPerson, true);

but with queries with join it produced the error as below:

ORA-00904: "LPLA_3"."ID_PERSON": invalid identifier

Is it problem with mapping?

Please show us the final code you are using, and the generated sql. Also please update to the latest version of the LLBLGen runtime library.

Stone: please use the latest runtime library build to see if this solves your problem. Also, it's easier for us if next time you start a new thread as the thread you're posting in doesn't really have a relation to your question, though by posting in this thread we still have to look at the previous posts to see if that's really the case.

Otis, sorry for wrong place where I'm posting. I will remember your suggestion next time. And my problem... I use the latest version of libraries. My case is a little more complicated then I have wiritten before. I can't write real entities' names so I have to change names. So I have Van entity which derives from Car entity which dervies from Vehicle entity, and a Person entity with relation 1:n with Van entity.

Linq query:

            var q = (from p in metaData.Person
                     join v in metaData.Van on p.IdPerson equals v.IdPerson
                     select p);

            int count = q.CountColumn(p => p.IdPerson, true);

generates sql as below:

 SELECT *
  FROM (SELECT DISTINCT COUNT(DISTINCT "LPLA_3"."ID_PERSON") AS "LPAV_"
          FROM "App"."Person" "LPA_L1",
               "App"."Van"  "LPA_L4",
               "App"."Car"  "LPA_L3",
               "App"."Vehicle"           "LPA_L2"
         WHERE "LPA_L1"."ID_PERSON" = "LPA_L4"."ID_PERSON"
           AND "LPA_L3"."ID_CAR = "LPA_L4"."ID_CAR"
           AND "LPA_L2"."ID_CAR" = "LPA_L3"."ID_CAR")
 WHERE rownum <= 1

There is wrong alias in count function.

Will look into it to see if we can reproduce this.

Reproduced:

[Test]
public void CountDistinctOnRelatedTypeOfInheritanceHierarchyTest()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        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);
        Assert.AreEqual(2, count);
    }
}

Looking into it.

(edit) I fixed it, but a testquery I wrote to test an alternative solution shows similar issues, so I've to look into that as well. The issue is that the main query q is a separate query, with a projection etc. and should be wrapped into a derived table. However the logic tries to be clever and wants to place the aggregate in the projection to avoid the derived table. This however goes wrong as the aliases in the aggregate are still pointing to the query q's alias.

A workaround exists, though it might be less optimal in your database: var count = metaData.Person.Where(p=>p.Cars.Count() > 0).CountColumn(p=>p.idPerson, true);

It doesn't use a main query with an added projection, but 1 query without a projection.

Please see the attached dll for the fix.

Thank you for help