У меня есть следующий запрос linq
var result = from myTypes in context.MyTypes
where
((myTypes .Prop1== "Test" ? 1 : 0) +
(myTypes .Prop2 == "Tester2" ? 1 : 0) +
(myTypes .Prop3 == "624642624000000000" ? 1 : 0) +
(myTypes .Prop4 == "TS166TH" ? 1 : 0) +
(myTypes .Prop5 == "1 Test Lane" ? 1 : 0)) >= 4
select new {
myTypes .Prop1,
myTypes .Prop2,
myTypes .Prop3,
myTypes .Prop4,
myTypes .Prop5,
myTypes .OtherProp,
myTypes .OtherTypeId
};
result.ToList();
Это приводит к следующему ожидаемому SQL, которого я хочу достичь
SELECT
[Extent1].[Prop1] AS [Prop1],
[Extent1].[Prop2] AS [Prop2],
[Extent1].[Prop3] AS [Prop3],
[Extent1].[Prop4] AS [Prop4],
[Extent1].[Prop5] AS [Prop5],
[Extent1].[OtherProp] AS [OtherProp],
[Extent1].[OtherTypeId] AS [OtherTypeId],
FROM [dbo].[MyType] AS [Extent1]
WHERE ((CASE WHEN ('Test' = [Extent1].[Prop1]) THEN 1 ELSE 0 END) + (CASE WHEN ('Tester2' = [Extent1].[Prop2]) THEN 1 ELSE 0 END) + (CASE WHEN ('624642624000000000' = [Extent1].[Prop3]) THEN 1 ELSE 0 END) + (CASE WHEN ('TS166TH' = [Extent1].[Prop4]) THEN 1 ELSE 0 END) + (CASE WHEN ('1 Test Lane' = [Extent1].[Prop5]) THEN 1 ELSE 0 END)) >= 4
Однако я хочу вернуть MyType не анонимный тип, поэтому я написал следующее
var result = from myTypes in context.MyTypes
where
((myTypes .Prop1== "Test" ? 1 : 0) +
(myTypes .Prop2 == "Tester2" ? 1 : 0) +
(myTypes .Prop3 == "624642624000000000" ? 1 : 0) +
(myTypes .Prop4 == "TS166TH" ? 1 : 0) +
(myTypes .Prop5 == "1 Test Lane" ? 1 : 0)) >= 4
select myTypes;
result.ToList();
Который, как я ожидал бы, сгенерирует тот же запрос, но вернет мой тип. Вместо этого я обнаружил, что он выполняет массивный рекурсивный запрос (по сути, запрашивая каждую запись в таблице), как показано ниже.
SELECT
[Extent1].[Prop1] AS [Prop1],
[Extent1].[Prop2] AS [Prop2],
[Extent1].[Prop3] AS [Prop3],
[Extent1].[Prop4] AS [Prop4],
[Extent1].[Prop5] AS [Prop5],
[Extent1].[OtherProp] AS [OtherProp],
[Extent1].[OtherTypeId] AS [OtherTypeId],
FROM [dbo].[MyType] AS [Extent1]
WHERE ((CASE WHEN ('Test' = [Extent1].[Prop1]) THEN 1 ELSE 0 END) + (CASE WHEN ('Tester2' = [Extent1].[Prop2]) THEN 1 ELSE 0 END) + (CASE WHEN ('624642624000000000' = [Extent1].[Prop3]) THEN 1 ELSE 0 END) + (CASE WHEN ('TS166TH' = [Extent1].[Prop4]) THEN 1 ELSE 0 END) + (CASE WHEN ('1 Test Lane' = [Extent1].[Prop5]) THEN 1 ELSE 0 END)) >= 4
GO
SELECT
[Extent1].[OtherTypeId] AS [OtherTypeId],
[Extent1].[OtherTypeProp] AS [OtherTypeProp],
FROM [dbo].[OtherTypes] AS [Extent1]
GO
-- Region Parameters
DECLARE EntityKeyValue1 BigInt = 1
-- EndRegion
SELECT
[Extent1].[Prop1] AS [Prop1],
[Extent1].[Prop3] AS [Prop2],
[Extent1].[Prop3] AS [Prop3],
[Extent1].[Prop4] AS [Prop4],
[Extent1].[Prop5] AS [Prop5],
[Extent1].[OtherProp] AS [OtherProp],
WHERE [Extent1].[OtherTypeId] = @EntityKeyValue1
GO
-- Region Parameters
DECLARE EntityKeyValue1 BigInt = 2
-- EndRegion
SELECT
[Extent1].[Prop1] AS [Prop1],
[Extent1].[Prop3] AS [Prop2],
[Extent1].[Prop3] AS [Prop3],
[Extent1].[Prop4] AS [Prop4],
[Extent1].[Prop5] AS [Prop5],
[Extent1].[OtherProp] AS [OtherProp],
WHERE [Extent1].[OtherTypeId] = @EntityKeyValue1
GO
Похоже, он получает все типы fk для КАЖДОЙ записи и игнорирует предложение where?
Может ли кто-нибудь объяснить, что я здесь делаю неправильно и почему два сгенерированных запроса разные?
MyType
код? - person Jacek Gorgoń   schedule 04.08.2011new MyType { … }
вместоnew { … }
! - person stakx - no longer contributing   schedule 05.08.2011