LINQ problem

Hi Guys,

here is the code:

public IQueryable<BLL.User> GetAllQueryable()

{

            var result = (from r in UOW.Registries

                          join u in UOW.Users on r.Id equals u.CID

                          select new BLL.User

                                     {

                                         ID = r.Id,

                                         Created = r.Created,

                                         CreatedBy = r.CreatedById,

                                         Deleted = r.Deleted,

                                         DeletedBy = r.DeletedById,

                                         TypeID = r.TypeId,

                                         Updated = r.Updated,

                                         UpdatedBy = r.UpdatedById,

                                         UserID = u.Id,

                                         FirstName = u.FirstName,

                                         LastName = u.LastName,

                                         Email = u.Email,

                                         Password = u.Password

                                     }

                         );

            return result;

}

this call works fine:

Engine.Orchestra.OrchestraUsers o = new OrchestraUsers();

var list = ( from b in o.GetAllQueryable() select b).Skip(1).Take(3).ToList();

Console.WriteLine(list.Count());

but this one doesn't

Engine.Orchestra.OrchestraUsers o = new OrchestraUsers();

var list = (

                from b in o.GetAllQueryable()

                where b.UserID==3

                select b).Skip(1).Take(3).ToList();

Console.WriteLine(list.Count());

giving me the following error

Unhandled Exception: System.NotSupportedException: Specified method is not suppo

rted.

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(IList`1 rang

eVariables, QuerySubexpressionCollection subexpressions, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(IList`1 rangeVar

iables, QuerySubexpressionCollection subexpressions, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractMemberCriteria(IList`1 ra

ngeVariables, QuerySubexpressionCollection subexpressions, MemberExpression memb

er)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ConvertToQueryExpression(IList`1

 rangeVariables, QuerySubexpressionCollection subexpressions, Expression express

ion)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractBinaryCriteria(IList`1 ra

ngeVariables, QuerySubexpressionCollection subexpressions, BinaryExpression expr

ession)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(IList`1 rang

eVariables, QuerySubexpressionCollection subexpressions, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(IList`1 rangeVar

iables, QuerySubexpressionCollection subexpressions, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(IList`1 rang

eVariables, QuerySubexpressionCollection subexpressions, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(IList`1 rangeVar

iables, QuerySubexpressionCollection subexpressions, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(IList`1 rang

eVariables, QuerySubexpressionCollection subexpressions, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(IList`1 rangeVar

iables, QuerySubexpressionCollection subexpressions, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.Where.Evaluate(ExpressionVisitor visitor, Me

thodCallExpression expression)

   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCall

Expression exp)

   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp)

   at Mindscape.LightSpeed.Linq.Sqo.Skip.Evaluate(ExpressionVisitor visitor, Met

hodCallExpression expression)

   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCall

Expression exp)

   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp)

   at Mindscape.LightSpeed.Linq.Sqo.Take.Evaluate(ExpressionVisitor visitor, Met

hodCallExpression expression)

   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCall

Expression exp)

   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp)

   at Mindscape.LightSpeed.Linq.LinqQueryProvider.GetExecutionPlan(Expression ex

pression)

   at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression)

   at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Exe

cute(Expression expression)

   at Mindscape.LightSpeed.Linq.LinqQuery`1.GetEnumerator()

   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)

   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

   at Sandbox.Experiments.test5() in D:\Development\net\experiments\DAL_MVC_EXTJ

S\Sandbox\Program.cs:line 39

   at Sandbox.Program.Main(String[] args) in D:\Development\net\experiments\DAL_

MVC_EXTJS\Sandbox\Program.cs:line 25

Press any key to continue . . .

any ideas why?

The reason for the exception is that the use of parameters in criteria is not supported.

This is where you have done a selection to a non entity type previously in the expression chain (e.g. you have selected the BLL.User object) and then you want to use one of its properties in a criteria statement.

I have added in support for this into tonights nightly build so if you grab that let us know how you get on.

Jeremy

The reason I asked is that this works well in MS EF. I'll download a new build today and try. I'll post back my results

Please note the new build (with the fix in it) won't be up until about 1500 GMT.

Hello.

I tested it today. It's working. Thanks for so quick reply.

But there has a error.

When i write code

(from b in o.GetAllQueryable()  where b.FirstName == "D" select b).Skip(100).Take(20).Count()

i give SQL

SELECT  COUNT(*) FROM  (
    SELECT t0.*  FROM Registry t0
     INNER JOIN      Users t1    ON      t0.ID = t1.ID
    ORDER BY      t0.ID  
    LIMIT 20 OFFSET 100
  )  t0
INNER JOIN  Users t1 ON  t0.ID = t1.ID
WHERE   t1.FirstName = 'D'

but i expect something like this

SELECT  COUNT(*)  FROM Registry t0
     INNER JOIN      Users t1    ON      t0.ID = t1.ID
     WHERE   t1.FirstName = 'D'
     LIMIT 20 OFFSET 100

WHERE  before LIMIT  and without second join.

other problem

CODE:

(from b in o.GetAllQueryable() where b.FirstName == "D" select b)                        .Skip(100).Take(20).Where(b=>b.LastName=="A").ToList()

heve:

SELECT ... FROM  Registry t0
INNER JOIN  Users t1 ON  t0.ID = t1.ID
WHERE (t1.FirstName = 'D' AND t1.LastName = 'A')
ORDER BY t0.ID
LIMIT 20 OFFSET 100

expect

SELECT ... FROM (
SELECT ... FROM  Registry t0
INNER JOIN  Users t1 ON  t0.ID = t1.ID
WHERE (t1.FirstName = 'D')
LIMIT 20 OFFSET 100
) WHERE  t1.LastName = 'A'

in the last two post i use 3.1.1711.14326 build

some ideas, comments, plans

Hi Alexander,

Yes, there would appear to be a different query strategy being applied when doing a Skip.Take.Where.Count as opposed to a Skip.Take.Where.Enumeration. We will need to put togethor a test case for this and then we can look at fixing this behavior. I will post an update once we either have a fix or more information on this.

Jeremy

any news on this?

Hi,

Yes, Jeremy added some enhancements yesterday - please try the latest nightly build and let us know if it resolved the issue for you.

Kind regards,

John-Daniel Trask

some ideas, comments, plans

Hi Alexander,

The second issue is a limitation of the LINQ provider in its translation of this type of query in that it is treating all of the directives under a single query rather than understanding that there should be a nested query scoped around the paging directives. We do have an item on the backlog to improve this in the future, however for now you would need to express such a query via the LightSpeed querying API:

e.g.

var query = new Query() { EntityType = typeof(User) };
query.QueryExpression = Entity.Attribute("FirstName") == "D";
query.Page = Page.At(100, 200);

var outerQuery = new Query() { EntityType = typeof(User) };
outerQuery.QueryExpression = Entity.Attribute("LastName") == "A";
outerQuery.InnerQuery = query;

var results = unitOfWork.Find(outerQuery);

Jeremy

i try use code like you recomend. I it get next SQL and error

Unhandled Exception: MySql.Data.MySqlClient.MySqlException: Unknown column 'users.UserID' in 'field list'

SELECT
  users.UserID AS "users.UserID",
  users.Email AS "users.Email",
  users.FirstName AS "users.FirstName",
  users.LastName AS "users.LastName",
  users.Password AS "users.Password"
FROM
  (
    SELECT
      users.UserID AS "users.UserID",
      users.Email AS "users.Email",
      users.FirstName AS "users.FirstName",
      users.LastName AS "users.LastName",
      users.Password AS "users.Password"
    FROM
      users
    WHERE
      (users.FirstName = 'D')
    LIMIT 200 OFFSET 100
  )
  users
WHERE
  users.Email = 'A'

it happened becouse  in first select field called like  "users.Name" ( for example "users.UserID AS "users.UserID"") but for second select there expected simple UserID (without "users.")

Hi Alexander,

Could you check this against a recent nightly build and see if this is still a problem for you as I am unable to reproduce this here.

If this is still a problem would you be able to send through a small sample model and query for me to reproduce this. I am just working off a local model using the query we had worked through earlier but I dont have any issues with the aliasing as you have described.

Thanks!

Jeremy

Hi Alexander,

You can email repro's and code samples to support@mindscape.co.nz

Please ensure you remove any executable files (.dll, .exe etc) as our mail server will likely block it even if it's inside a zip file. Or, alternatively, you can rar your files as it doesn't seem to check them (good old Google mail services! :)

Thanks,

John-Daniel

Hi Alexander,

I just wanted to update you that we have put in a candidate fix for this issue into tonights nightly build (20100906).

Thanks again for the precise repro on this!

Jeremy