This question is locked. New answers and comments are not allowed.
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:
OA logs and LinqPad show following sql query:
And it works as expected -- return all 8 rows (see above).
Second query:
Logs show following sql:
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:
There are obvious performance penalties...
Thanks in advance!
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!