Sqlite. Linq queries. Two questions.

[Jose Mejia]

Hello.

I have two equivalent linq queries (well, I think so...) -- but they give different results.

The relevant data is:

id = 1    name = "asd"
id = 2       name = "dsa"
id = 743  name = "d"
id = 4    name = "test"
id = 776  name = "testeg"
id = 3    name = "some"
id = 777  name = "My"
id = 739  name = "s"

First query is:

IQueryable<HighWay> highWays = null;
foreach (var str in new[] { "d", "test", "m","s" })
{
  string str1 = str;
  var temp = entitiesModel.HighWays.Where(t => t.Name.Contains(str1));
  highWays = highWays == null ? temp : highWays.Union(temp);
}

OA logs and LinqPad show following sql query:

По логам ORM и linqpad'а генерируется sql:

SELECT t0.[Geometry], t0.[Name], t0.[PK_UID]
FROM [HighWays] AS t0
WHERE Like('%' || @p0 || '%', t0.[Name])
-- p0 = [d]

SELECT t0.[Geometry], t0.[Name], t0.[PK_UID]
FROM [HighWays] AS t0
WHERE Like('%' || @p0 || '%', t0.[Name])
-- p0 = [test]

SELECT t0.[Geometry], t0.[Name], t0.[PK_UID]
FROM [HighWays] AS t0
WHERE Like('%' || @p0 || '%', t0.[Name])
-- p0 = [m]

SELECT t0.[Geometry], t0.[Name], t0.[PK_UID]
FROM [HighWays] AS t0
WHERE Like('%' || @p0 || '%', t0.[Name])
-- p0 = [s]

And it works as expected -- return all 8 rows (see above).

Second query:

var test = new[] { "d","test","s","m"};
var   el = from s1 in test
        from entity in HighWays
        where entity.Name.Contains(s1)
        select entity;

Logs show following sql:

SELECT t0.[Geometry], t0.[Name], t0.[PK_UID]
FROM [HighWays] AS t0

SELECT t0.[Geometry], t0.[Name], t0.[PK_UID]
FROM [HighWays] AS t0

SELECT t0.[Geometry], t0.[Name], t0.[PK_UID]
FROM [HighWays] AS t0

SELECT t0.[Geometry], t0.[Name], t0.[PK_UID]
FROM [HighWays] AS t0

That query didn't return row  id = 777  name = "My" , as result 7 rows instead of 8.
In other words, the result for the second query is the same if I removed item "m" from array "test".

Why they are differ?

The other question is related to the first query:
it seems that there are four (according to logs)  sql queris issued to db, instead of one. Why generated query
doesn't use Sql UNION and combine everythin in one query?
Smth like:

SELECT t0.[Geometry], t0.[Name], t0.[PK_UID]
FROM [HighWays] AS t0
WHERE Like('%m%', t0.[Name])
UNION
SELECT t0.[Geometry], t0.[Name], t0.[PK_UID]
FROM [HighWays] AS t0
WHERE Like('%s%', t0.[Name])
...

 There are obvious performance penalties...

Thanks in advance!

[Jose Mejia]

Sorry, I forgot about OA Linq forum. You may move this thread to Linq forum...

[Jose Mejia]

The problem according different behavior of queries is solved.

var test = new[] { "d","test","s","m"};
var   el = from s1 in test
        from entity in HighWays
        where entity.Name.Contains(s1)
        select entity;

Query is working on IEnumerables, not on IQueryable. So it doesn't generate sql with Like.

But I'm still interested in second question regarding multiple queries vs one with sql Union.

[Alexander]

Hello Jose,

This seems to be an unsupported scenario for our linq provider, that is why the generated SQL is not optimal and does not use the UNION operator.

As a more optimal solution for the moment I would suggest you to use code like this:

EntitiesModel context = new EntitiesModel();

var strings = new string[] { "a", "b", "c" };

Expression<Func<Order, bool>> predicate = o => false;
foreach(string s in strings)
{
    Expression<Func<Order, bool>> filter = o => o.ShipCity.Contains(s);
    predicate = Expression.Lambda<Func<Order, bool>>(Expression.Or(predicate.Body, filter.Body), predicate.Parameters);
}

var query = context.Orders.Where(predicate);

string sql = query.ToString();
var result = query.ToList();

This code creates dynamically a separate where condition for each string in the array and appends it to the query with an OR operator. You can check the generated SQL query in the sql string variable.
Hope that helps.


Greetings,
Alexander
the Telerik team

[Jose Mejia]

Thank you so much for clear explanation.

One more question. Suppose following query:

from entity in entitiesModel.HighWays
from s1 in new[] { "d", "test", "m", "s" }
where entity.Name.Contains(s1)
select entity;

I just simply revert  first two lines in previous query.

It throws:
An exception occured during the execution of 'Extent<TestSpatialite.HighWay>().SelectMany(entity => new [] {"d", "test", "m", "s"}, (entity, s1) => new <>f__AnonymousType0`2(entity = entity, s1 = s1)).Where(<>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.entity.Name.Contains(<>h__TransparentIdentifier0.s1)).Select(<>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.entity)'. Failure: Unable to formulate a join to express the SelectMany(entity => new [] {"d", "test", "m", "s"}).
See InnerException for more details.
Complete Expression:
.Call System.Linq.Queryable.Select(
    .Call System.Linq.Queryable.Where(
        .Call System.Linq.Queryable.SelectMany(
            .Constant<Telerik.OpenAccess.Query.ExtentQueryImpl`1[TestSpatialite.HighWay]>(Extent<TestSpatialite.HighWay>()),
            '(.Lambda #Lambda1<System.Func`2[TestSpatialite.HighWay,System.Collections.Generic.IEnumerable`1[System.String]]>),
            '(.Lambda #Lambda2<System.Func`3[TestSpatialite.HighWay,System.String,<>f__AnonymousType0`2[TestSpatialite.HighWay,System.String]]>))
        ,
        '(.Lambda #Lambda3<System.Func`2[<>f__AnonymousType0`2[TestSpatialite.HighWay,System.String],System.Boolean]>)),
    '(.Lambda #Lambda4<System.Func`2[<>f__AnonymousType0`2[TestSpatialite.HighWay,System.String],TestSpatialite.HighWay]>))

.Lambda #Lambda1<System.Func`2[TestSpatialite.HighWay,System.Collections.Generic.IEnumerable`1[System.String]]>(TestSpatialite.HighWay $entity)
{
    .NewArray System.String[] {
        "d",
        "test",
        "m",
        "s"
    }
}
...

I'm not quite understand why it throws and where problem is? Or it is simply problem with OA linq provider?...

Thanks in advance.

[Alexander]

Hi Jose,

From Linq point of view, this is a perfectly valid query. From OpenAccess' perspective however, things are more complex. OpenAccess needs to translate the linq query to SQL query and try to push as many operations as possible to the server (filtering, sorting, grouping, etc) for optimal performance.

For a query like yours, OpenAccess would normally generate a join on two tables in the SQL query. The difference in this case is that there is no second table but an in-memory structure, which cannot be joined on the server. That is why the error states that OpenAccess is unable to formulate the join, which is expected.

The previous query:

from s1 in test
from entity in context.HighWays
where entity.Name.Contains(s1)
select entity;

is also different because the OpenAccess' extent is joined on the second place. This means that the query is executed in memory (Linq to Objects), because the first used extent is the array, and context.HighWays is just enumerated. Enumerating the OpenAccess endpoint actually results in some SQL queries being executed, because the persistent objects should be loaded, but the filtering is also done entirely in memory.
I hope this clarifies better how these queries work.

All the best,
Alexander
the Telerik team