Вот какие левые запросы с условием на неравенство получились
var бд = Константы1С.ГлобальныйКонтекст.БД;
var qr = from Номенклатура in бд.Спр_Номенклатура
from единицы in бд.Спр_Единицы.Where(единица => единица.ВладелецId == Номенклатура.ID && единица.ШтрихКод.CompareTo("4") > 0).DefaultIfEmpty()
select new { Номенклатура.Наименование,
Номенклатура.ПолнНаименование,
единицы.ШтрихКод,
ОКЕИ=единицы.ОКЕИ.Наименование
};
foreach (var элем in qr.Take(1000))
{
if (элем.ШтрихКод == null)
continue;
Console.WriteLine("{0}.{1} - {2}", элем.Наименование.TrimEnd(), элем.ШтрихКод == null ? "null" : элем.ШтрихКод, элем.ПолнНаименование.TrimEnd(), элем.ОКЕИ == null ? "null" : элем.ОКЕИ);
}
Показать
Выдает такой запрос
SELECT
[Limit1].[C1] AS [C1],
[Limit1].[DESCR] AS [DESCR],
[Limit1].[SP101] AS [SP101],
[Limit1].[SP80] AS [SP80],
[Limit1].[DESCR1] AS [DESCR1]
FROM ( SELECT TOP (1000)
[Extent1].[DESCR] AS [DESCR],
[Extent1].[SP101] AS [SP101],
[Extent2].[SP80] AS [SP80],
[Extent3].[DESCR] AS [DESCR1],
1 AS [C1]
FROM [dbo].[SC84] AS [Extent1]
LEFT OUTER JOIN [dbo].[SC75] AS [Extent2] ON ([Extent2].[PARENTEXT] = [Extent1].[ID]) AND ([Extent2].[SP80] > N'4')
LEFT OUTER JOIN [dbo].[SC41] AS [Extent3] ON [Extent2].[SP79] = [Extent3].[ID]
) AS [Limit1]
Показать Или больше мне нравится таой запрос
var бд = Константы1С.ГлобальныйКонтекст.БД;
var qr = from Номенклатура in бд.Спр_Номенклатура
select new
{
Номенклатура.Наименование,
Номенклатура.ПолнНаименование,
Единицы = (Номенклатура.ПодчиненныеЕдиницы.Where(единица => единица.ШтрихКод.CompareTo("4") > 0)
.Select(единица =>
new
{
единица.ШтрихКод,
ОКЕИ = единица.ОКЕИ.Наименование
}
)
)
};
foreach (var элем in qr.Take(1000))
{
foreach (var единица in элем.Единицы)
Console.WriteLine("{0}.{1} - {2}", элем.Наименование.TrimEnd(), единица.ШтрихКод, элем.ПолнНаименование.TrimEnd(), единица.ОКЕИ);
}
Показать
Генерирут SQL запрос
SELECT
[Project2].[ID] AS [ID],
[Project2].[C1] AS [C1],
[Project2].[DESCR] AS [DESCR],
[Project2].[SP101] AS [SP101],
[Project2].[C2] AS [C2],
[Project2].[SP80] AS [SP80],
[Project2].[DESCR1] AS [DESCR1]
FROM ( SELECT
[Limit1].[ID] AS [ID],
[Limit1].[DESCR] AS [DESCR],
[Limit1].[SP101] AS [SP101],
[Limit1].[C1] AS [C1],
[Join1].[SP80] AS [SP80],
[Join1].[DESCR] AS [DESCR1],
CASE WHEN ([Join1].[PARENTEXT] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
FROM (SELECT TOP (1000)
[Extent1].[ID] AS [ID],
[Extent1].[DESCR] AS [DESCR],
[Extent1].[SP101] AS [SP101],
1 AS [C1]
FROM [dbo].[SC84] AS [Extent1] ) AS [Limit1]
LEFT OUTER JOIN (SELECT [Extent2].[PARENTEXT] AS [PARENTEXT], [Extent2].[SP80] AS [SP80], [Extent3].[DESCR] AS [DESCR]
FROM [dbo].[SC75] AS [Extent2]
INNER JOIN [dbo].[SC41] AS [Extent3] ON [Extent2].[SP79] = [Extent3].[ID] ) AS [Join1] ON ([Limit1].[ID] = [Join1].[PARENTEXT]) AND ([Join1].[SP80] > N'4')
) AS [Project2]
ORDER BY [Project2].[ID] ASC, [Project2].[C2] ASC Показать