0

I want to translate SQL function to C# code.

I have a myCpRegs as a list from my own class, that is what I get from [fn_TrackingFromCpReg_CpRegPos] (@dtFrom, @dtTo) function.

How to write this statement with a left outer join in EF Core?

insert into TrackingHistoryPos (TrackingHistoryID, Obj_ContID, Obj_TrayConcID, Obj_ProdID, Obj_CostCentreID, HasSN, Qty,
            Parent_ContID, StockLocationIDFrom, StockLocationID, 
            IsDeleted, TrackTime, IsPreliminary, IsPhysical)
    select
        B.TrackingHistoryID, A.Obj_ContID, A.Obj_TrayConcID, A.Obj_ProdID, A.Obj_CostCentreID, A.HasSN, A.Qty,
        A.Parent_ContID, A.StockLocationIDFrom, A.StockLocationID, 
        A.IsDeleted, A.TrackTime, A.IsPreliminary, A.IsPhysical
    from 
        [fn_TrackingFromCpReg_CpRegPos] (@dtFrom, @dtTo) A
    left outer join 
        TrackingHistory B on A.CpRegID = B.CpRegID
    order by 
        A.CpRegPosID

This is what I tried to do

trackingHistoryPos = myCpRegs
    .GroupJoin(trackings, c => c.CpRegID, t => t.CpRegID
    myCpRegs.Where(x => (x.Pos_Obj_TrayConcID ?? x.Pos_Obj_ContID) is not null)
    .Select(x => new TrackingHistoryPosRow()
    {
        TrackingHistoryID = , // TODO
        Obj_ContID = x.Pos_Obj_ContID,
        Obj_TrayConcID = x.Pos_Obj_TrayConcID,
        Obj_ProdID = x.Pos_Obj_ProdID,
        Obj_CostCentreID = , //TODO
        HasSN = x.Pos_Obj_TrayConcID is not null && (x.Pos_Obj_TrayHasSN ?? false),
        Qty = x.Pos_Qty,
        Parent_ContID = , //TODO
        StockLocationIDFrom = , //TODO
        StockLocationID = , //TODO
        IsDeleted = x.IsRejected == 1,
        TrackTime = x.TrackTime,
        IsPreliminary = x.IsPreliminary == 1,
        IsPhysical = x.IsPhysical == 1,
    })
    ).ToList();

I tried this solution and I become an error

                var query = from c in myCpRegs
                            join t in trackingsHistory on c.CpRegID equals t.CpRegID into ps_jointable
                            from ct in ps_jointable.DefaultIfEmpty()
                            orderby c.Pos_CpRegPosID
                            select new TrackingHistoryPosRow()
                            {
                                TrackingHistoryID = ct.TrackingHistoryID, // TODO
                                Obj_ContID = c.Pos_Obj_ContID,
                                Obj_TrayConcID = c.Pos_Obj_TrayConcID,
                                Obj_ProdID = c.Pos_Obj_ProdID,
                                //Obj_CostCentreID = , //TODO
                                HasSN = c.Pos_Obj_TrayConcID is not null && (c.Pos_Obj_TrayHasSN ?? false),
                                Qty = c.Pos_Qty,
                                //Parent_ContID = , //TODO
                                //StockLocationIDFrom = , //TODO
                                //StockLocationID = , //TODO
                                IsDeleted = c.IsRejected == 1,
                                TrackTime = c.TrackTime,
                                IsPreliminary = c.IsPreliminary == 1,
                                IsPhysical = c.IsPhysical == 1,
                            };

                trackingHistoryPos = query.ToList();
Anas Sadek
  • 37
  • 5
  • 2
    As architecture suggestion, db work should be in the db side. This will only lead to bad performance with large data and a lot of headaches – Leandro Bardelli Feb 24 '23 at 14:47
  • @LeandroBardelli I have to make everything in c# because I have to a very big Data and don't want to overload the server – Anas Sadek Feb 24 '23 at 15:15
  • 2
    If you have *"very big Data"* and you claim you *"don't want to overload the server"*, then Leandro Bardelli's [statement](https://stackoverflow.com/questions/75557851/left-outer-join-in-ef-core#comment133306200_75557851) is all the more relevant. – madreflection Feb 24 '23 at 16:05
  • 2
    EF-Core will convert this C# LINQ query to an SQL Statement that will be executed on the Server anyway. No matter how you do it, the records will have to be queried and inserted by the server. Doing it all in a stored proc will save you from transferring data between the server and your C# app. – Olivier Jacot-Descombes Feb 24 '23 at 16:13
  • 1
    You should check the amount of data that EF can handle, if you don't have any options you should think in other architecture solution with a different database as index or if in C#, work with ADO.NET – Leandro Bardelli Feb 24 '23 at 20:15

0 Answers0