select one to many how to write

[quan01994]

my code

                          from emp in db.EmployeeInfo
                          from branch in db.BranchInfo
                          from att in (
                              from attach in db.Attachment
                              where attach.Type == 1 && attach.Status == 0 && attach.ItemId == emp.EmpId
                              group attach by attach.ItemId into g
                              select new
                              {
                                  EmpId  =g.Key,
                                  EmpPic = g.Select(m=>m.Url).AsQueryable(),
                              })
                          where emp.EmpCode == "1002" && emp.Status == 0 && emp.BranchCode == branch.BranchCode
                          select new EmployeeOutput
                          {
                              EmpAvatar = emp.EmpAvatar,
                              EmpCode = emp.EmpCode,
                              EmpId = emp.EmpId,
                              EmpName = emp.EmpName,
                              EmpPhone = emp.EmpPhone,
                              EmpPic = att.EmpPic.ToList(),
                              BranchAddress = branch.BranchAddress,
                              BranchName = branch.BranchName
                          };
                var temp = qry.ToLinqToDB().FirstOrDefault();

translate sql

SELECT TOP (1)
    [t4].[EmpAvatar],
    [t4].[EmpCode],
    [t4].[EmpId],
    [t4].[EmpName],
    [t4].[EmpPhone],
    [t3].[c1] as [c11],
    [t1].[BranchAddress],
    [t1].[BranchName]
FROM
    [EmployeeInfo] [t4]
        CROSS JOIN [BranchInfo] [t1]
        CROSS APPLY (
            SELECT
                (
                    SELECT
                        [keyParam].[Url]
                    FROM
                        [Attachment] [keyParam]
                    WHERE
                        [t2].[ItemId] = [keyParam].[ItemId] AND
                        [keyParam].[Type] = 1 AND
                        [keyParam].[Status] = 0 AND
                        [keyParam].[ItemId] = [t4].[EmpId]
                ) as [c1]
            FROM
                [Attachment] [t2]
            WHERE
                [t2].[Type] = 1 AND [t2].[Status] = 0 AND [t2].[ItemId] = [t4].[EmpId]
            GROUP BY
                [t2].[ItemId]
        ) [t3]
WHERE
    [t4].[EmpCode] = N'1002' AND [t4].[Status] = 0 AND
    [t4].[BranchCode] = [t1].[BranchCode]

error .

子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

[quan01994]

use ef it's run , but ef generate two sql to exec and ef is loading all records as objects into memory for group by

[sdanyliv]

It's Eager Loading which we do not support. It should be processed by EF Core if you remove complexity. Place details closer to projection and remove grouping - it is not needed here.

var qry =
            from emp in db.EmployeeInfo
            from branch in db.BranchInfo
            where emp.EmpCode == "1002" && emp.Status == 0 && emp.BranchCode == branch.BranchCode
            select new EmployeeOutput
            {
                EmpAvatar = emp.EmpAvatar,
                EmpCode = emp.EmpCode,
                EmpId = emp.EmpId,
                EmpName = emp.EmpName,
                EmpPhone = emp.EmpPhone,
                EmpPic = (from attach in db.Attachment
                    where attach.Type == 1 && attach.Status == 0 && attach.ItemId == emp.EmpId
                   select attach.Url).ToList(),
                BranchAddress = branch.BranchAddress,
                BranchName = branch.BranchName
            };
var temp = qry.FirstOrDefault();

[quan01994]

ok. It's like the best way to do it now.

[quan01994]

LinqToDBForEFTools.Initialize(); It's global ? Initialization of the first run of my project

[sdanyliv]

Yes it is global, but it is idempotent, so you can call as many times as you need. It is required only if you use linq2db extensions Delete, Update, Insert etc. and do not call ToLinqToDB() method before. LinqToDBForEFTools.Initialize() also called automatically from LinqToDBForEFTools static constructor. So usually it is already initialized.

[sdanyliv]

Closing for now. Will reopen if needed.