.net EntityFramework, где через многие ко многим соединяются ссылки и вложенные таблицы

Question

SQL-запросы, такие как приведенный ниже, но с использованием структуры сущностей?

SELECT     Software.Vendor, Software.Version, 
Rollout.ID, Software.Name, Rollout.Name 

FROM         org_User INNER JOIN
                      SoftwareUser ON org_User.ID = SoftwareUser.FK_org_User_ID INNER JOIN
                      Software ON Software.ID = SoftwareUser.FK_Software_ID INNER JOIN
                      AssetAssignee ON org_User.ID = AssetAssignee.FK_org_User_ID INNER JOIN
                      Rollout ON AssetAssignee.FK_Rollout_ID = Rollout.ID INNER JOIN
                      Location ON AssetAssignee.FK_Location_ID = Location.ID
WHERE     (Rollout.ID = 44) AND (Location.ID = 2)
GROUP BY Software.Vendor, Software.Version, 
Rollout.ID, Software.Name, Rollout.Name 

Схема БД (обратите внимание на таблицу "многие для многих" "SoftwareUser")

Модель Entm Framework edmx (таблица "многие пользователи" "SoftwareUser" не отображается, так как дизайнер признает ее просто таблицей ссылок "многие ко многим" и иллюстрирует это строкой соединения со звездочкой на обоих концах)

Я попытался использовать служебный класс PredicateBuilder, но то, как я это сделал ниже, стало совершенно неверным, поскольку я не мог заставить его построить запрос так, как я его использую. Так что радости нет.

 internal static List<Software> GetApplications(
        string UserID,
        string Surname,
        string Forname,
        int? TeamID,
        int? LocationID,
        int? RolloutID,
        string EmployeeID)
    {
        {
            var predicate = GetApplicationsPredicate(UserID, Surname, Forname, TeamID, LocationID, RolloutID, EmployeeID);

            using (ITAMEFContext db = new ITAMEFContext(ConnectionStrings.ITAM_ConnectionString_EF))
            {
                db.Configuration.ProxyCreationEnabled = false;

                return db.Softwares.Where(predicate).ToList();

            }
        }
    }

    private static Expression<Func<Software, bool>> GetApplicationsPredicate(
        string UserID,
        string Surname,
        string Forname,
        int? TeamID,
        int? LocationID,
        int? RolloutID,
        string EmployeeID)
    {
        var predicate = PredicateBuilder.True<Software>();

        bool nothingSelected = (
            TeamID == null
            && LocationID == null
            && RolloutID == null
            && string.IsNullOrEmpty(Surname)
            && string.IsNullOrEmpty(Forname)
            && string.IsNullOrEmpty(EmployeeID)
            && string.IsNullOrEmpty(UserID)
            );

        if (nothingSelected)
        {
            // we need to negate the first true predicate of we don't have any other predicates.
            predicate = predicate.And(u => !(nothingSelected));
            return predicate;
        }

        //if (!string.IsNullOrEmpty(UserID))
        //    predicate = predicate.And(s => s.org_User.Select(u => u.AssetAssignees.Any(aa => aa.org_User.DomainUserID.Contains(UserID))) != null);

        //if (!string.IsNullOrEmpty(Surname))
        //    predicate = predicate.And(s => s.org_User.Any(u => u.AssetAssignees.Any(aa => aa.org_User.Surname.Contains(Surname))));

        //if (!string.IsNullOrEmpty(Forname))
        //    predicate = predicate.And(s => s.org_User.Any(u => u.AssetAssignees.Any(aa => aa.org_User.Forename.Contains(Forname)))); ;

        //if (!string.IsNullOrEmpty(EmployeeID))
        //    predicate = predicate.And(s => s.org_User.Any(u => u.AssetAssignees.Any(aa => aa.org_User.EmployeeID.Contains(EmployeeID))));

        if (LocationID.HasValue)
            predicate = predicate.And(s => s.org_User.Any(u => u.AssetAssignees.Any(aa => aa.Location.ID == LocationID.Value)));

        //if (TeamID.HasValue)
        //    predicate = predicate.And(s => s.org_User.Any(u => u.AssetAssignees.Any(aa => aa.org_User.org_Team.ID == TeamID.Value)));

        if (RolloutID.HasValue)
            predicate = predicate.And(s => s.org_User.Any(u => u.AssetAssignees.Any(aa => aa.Rollout.ID == RolloutID)));

        //if (RolloutID.HasValue)
        //    predicate = predicate.And(s => s.org_User.Any(u => u.AssetAssignees.Any(aa => aa.Rollout.ID == RolloutID)));

        return predicate;
    }

Но это только привело к тому, что генерируется следующий неподходящий SQL.

    SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[FK_ApptrackerID] AS [FK_ApptrackerID], 
[Extent1].[FK_SoftwareType_ID] AS [FK_SoftwareType_ID], 
[Extent1].[Name] AS [Name], 
[Extent1].[Vendor] AS [Vendor], 
[Extent1].[Version] AS [Version], 
[Extent1].[Ready] AS [Ready]
FROM [dbo].[Software] AS [Extent1]
WHERE 
( EXISTS (SELECT 
    1 AS [C1]
    FROM ( SELECT 
        [Extent2].[FK_org_User_ID] AS [FK_org_User_ID]
        FROM [dbo].[SoftwareUser] AS [Extent2]
        WHERE [Extent1].[ID] = [Extent2].[FK_Software_ID]
    )  AS [Project1]
    WHERE  EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[AssetAssignee] AS [Extent3]
        WHERE ([Project1].[FK_org_User_ID] = [Extent3].[FK_org_User_ID]) AND ([Extent3].[FK_Location_ID] = 2)
    )
)) 

AND 

( EXISTS (SELECT 
    1 AS [C1]
    FROM ( SELECT 
        [Extent4].[FK_org_User_ID] AS [FK_org_User_ID]
        FROM [dbo].[SoftwareUser] AS [Extent4]
        WHERE [Extent1].[ID] = [Extent4].[FK_Software_ID]
    )  AS [Project4]
    WHERE  EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[AssetAssignee] AS [Extent5]
        WHERE ([Project4].[FK_org_User_ID] = [Extent5].[FK_org_User_ID]) AND ([Extent5].[FK_Rollout_ID] = 44)
    )
))

пожалуйста помоги:'(

EF dll версия 4.4.0.0 | Метод - база данных сначала | .net 4.0

Answer 1

В конце я остановился на следующем синтаксисе запроса, который возвращает правильные результаты.

internal static List<Software> GetApplications(
            string UserID,
            string Surname,
            string Forname,
            int? TeamID,
            int? LocationID,
            int? RolloutID,
            string EmployeeID)
        {
            {

                using (ITAMEFContext db = new ITAMEFContext(ConnectionStrings.ITAM_ConnectionString_EF))
                {
                    db.Configuration.ProxyCreationEnabled = false;

                    return (from ou in db.org_User
                           from sw in ou.Softwares
                           from aa in ou.AssetAssignees
                           where aa.org_User.DomainUserID.Contains(UserID)
                           &&
                           (!TeamID.HasValue || aa.org_User.org_Team.ID == TeamID)
                           &&
                           (!LocationID.HasValue || aa.org_User.Location.ID == LocationID.Value)
                           &&
                           (!RolloutID.HasValue || aa.Rollout.ID == RolloutID)
                           &&
                           aa.org_User.Surname.Contains(Surname)
                           &&
                           aa.org_User.Forename.Contains(Forname)
                           &&
                           aa.org_User.EmployeeID.Contains(EmployeeID)
                           group sw by sw.ID into swG

                           select swG).Select(g => g.FirstOrDefault()).ToList();

                }
            }
        }

Answer 2

Есть запросы, которые невозможно написать в EF, но запрос, который вы показываете, на самом деле не такой сложный, особенно. не при использовании синтаксиса запроса и использовании возможностей навигационных свойств:

from ou in db.org_User
from sw in ou.Softwares
from aa in ou.AssetAssignees
select new { sw.Vendor, sw.Version, aa.Rollout.ID, aa.Rollout.Name }

Вам не нужно group by потому что вы выбираете точно те же поля, что и те, по которым вы группируете. Вам также не нужен доступ к соединительной таблице, потому что EF выполняет присоединение за вас.

Если вы хотите применить предикаты, вы можете начать утверждение с

from ou in db.org_User.Where(predicate)