Организация Entity Framework: таблица соединения в другую таблицу с помощью аналогичного преобразования SQL в LINQ [закр ⇐ C#

[Anonymous]

У меня довольно уродливое утверждение SQL, которое я пытаюсь преобразовать в LINQ (.net 6), но я изо всех сил пытаюсь его преобразовать. < /p>
Ошибка, которую я получаю: < /p>
The LINQ expression 'CROSS APPLY (Projection Mapping:
CategoryId -> c.Id
CategoryName -> c.Name
SELECT c.Id AS CategoryId, c.Name AS CategoryName
FROM categories AS c
WHERE (c.lft == (c.rgt - 1)) && (e.CleanDescription LIKE ('%' + COALESCE(c.Name, '')) + '%')
UNION ALL
Projection Mapping:
CategoryId -> c0.id
CategoryName -> c0.name
SELECT c0.id AS CategoryId, c0.name AS CategoryName
FROM category_labels AS c0
LEFT JOIN categories AS c1 ON c0.categoryId == c1.Id
WHERE ((c1.lft == (c1.rgt - 1)) || (Equal(c1.lft) && Equal(c1.rgt))) && (e.CleanDescription LIKE ('%' + c0.name) + '%')
) AS t
' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
< /code>
может кто-нибудь помочь?SELECT
r.id, r.Name,r.Description,
IF (MAX(r.percentageMatch) < 65 || r.CategoryId IS NULL, "fb319c1d-13a9-47e1-a5dc-0e4c4a9a7c13",r.CategoryId),
r.CategoryName, MAX(r.percentageMatch)
FROM
(SELECT
ei.Id, ei.Name, ei.Description, ei.CleanDescription,
c.Id as CategoryId, c.Name as CategoryName,
((LENGTH(ei.CleanDescription) - (locate(c.name, ei.CleanDescription -1)) / LENGTH(ei.CleanDescription) * 100) * 0.4) + ((LENGTH(c.Name) / LENGTH(ei.CleanDescription) * 100) * 0.6) AS PercentageMatch
FROM
(SELECT
e.Id, e.Name, e.Description, e.CleanDescription
FROM
equipments e
WHERE
e.EquipmentCategoryId = "fb319c1d-13a9-47e1-a5dc-0e4c4a9a7c13" OR e.EquipmentCategoryId = NULL) ei
LEFT JOIN
(SELECT c.Id, c.Name
FROM categories c
WHERE c.Lft = c.rgt-1
UNION
SELECT c.Id, l.Name
FROM category_labels l
INNER JOIN categories c ON c.Id = l.categoryId
WHERE c.Lft = c.rgt-1) c ON ei.CleanDescription LIKE CONCAT("%",c.Name,"%")
) r
GROUP BY
r.id, r.Name, r.Description
< /code>
Я пробовал следующее без успеха и просто задаюсь вопросом, проще ли использовать хранимую процедуру или выполнить выполнение Rawsql?_db.Equipments.Include(i => i.EquipmentCategory)
.SelectMany(m =>
_db.Categories
.Where(n => n.Left == n.Right - 1 && EF.Functions.Like(m.CleanDescription, "%"+n.Name+"%"))
.Select(s => new { CategoryId = s.Id, CategoryName = s.Name })
.Concat(
_db.CategoryLabels
.Where(n => n.Category.Left == n.Category.Right - 1 && EF.Functions.Like(m.CleanDescription, "%" + n.Name + "%"))
.Select(s => new { CategoryId = s.Id, CategoryName = s.Name })
).Select(s=> new { Equipment = m, Category = s, Percentage = (((m.CleanDescription.Length - (m.CleanDescription.IndexOf(s.CategoryName) - 1)) / m.CleanDescription.Length * 100 )* 0.4) +
((s.CategoryName.Length / m.CleanDescription.Length * 100) * 0.6)
})
).ToList();
< /code>
Обновление 1 < /strong> < /p>
Я попробовал swyatoslav предложение, но я все еще получаю следующую ошибку: < /p>

Я не должен иметь значения < /p>
< /blockquote>
. Отсутствует. < /p>
(from equipment in _db.Equipments
from c in (
(from catInner in _db.Categories
where catInner.Left == catInner.Right - 1// && equipment.CleanDescription == catInner.Name
select new Category
{
Id = catInner.Id,
Left = catInner.Left,
Right = catInner.Right,
Name = catInner.Name,
RootId = catInner.RootId,
ParentId = catInner.ParentId,
Level = catInner.Level,
Updated = catInner.Updated,
Created = catInner.Created
})
.Union(
from catLabel in _db.CategoryLabels
where catLabel.Category.Left == catLabel.Category.Right - 1// && equipment.CleanDescription == catLabel.Name
select new Category {
Id = catLabel.Id, Left = catLabel.Category.Left,
Right = catLabel.Category.Right,
Name = catLabel.Category.Name,
RootId = catLabel.Category.RootId,
ParentId = catLabel.Category.ParentId,
Level = catLabel.Category.Level,
Updated = catLabel.Category.Updated, Created = catLabel.Category.Created
})
).Where(c => equipment.CleanDescription == c.Name).DefaultIfEmpty()
let parent = c
where equipment.EquipmentCategoryId == null || equipment.EquipmentCategoryId == Guid.Empty || equipment.EquipmentCategoryId == CategoryId.Unkown
select new { Equipment = equipment, Category = parent }
).ToList();


Подробнее здесь: https://stackoverflow.com/questions/725 ... ow-to-conv