How to get rid of multiple left join in EF core eager loading for same tables-CodePudding

I'm converting Oracle Sp to EFCore with eager loading but I wonder that how to convert below query with more efficiently.

My main aim is code it more clean and learn a better way if it's possible. It's leads to create many MRC_BATCH_INFO instances for .Include() which is not I want.

              SELECT C.COLL_ID,
                MI.MRC_INC_NAME,
                ME.MRC_EXC_NAME,
                SI.STR_INC_NAME,
                SE.STR_EXC_NAME,
                AI.MSTR_INC_NAME,
                AE.MSTR_EXC_NAME,
                FROM MERCHANT C
               ,MRC_BATCH_INFO MI
               ,MRC_BATCH_INFO ME
               ,MRC_BATCH_INFO SI
               ,MRC_BATCH_INFO SE
               ,MRC_BATCH_INFO AI
               ,MRC_BATCH_INFO AE
              WHERE C.COLL_ID = PI_COLL_ID
                AND C.MRC_INC_LIST_ID = MI.COLL_MRC_BATCH_ID( )
                AND C.MRC_EXC_LIST_ID = ME.COLL_MRC_BATCH_ID( )
                AND C.STR_INC_LIST_ID = SI.COLL_MRC_BATCH_ID( )
                AND C.STR_EXC_LIST_ID = SE.COLL_MRC_BATCH_ID( )
                AND C.MSTR_INC_LIST_ID = AI.COLL_MRC_BATCH_ID( )
                AND C.MSTR_EXC_LIST_ID = AE.COLL_MRC_BATCH_ID( )


var result = await _mrcRepository
             .Include(x=>x.MrcBatchInfo_MI)  // I want to get rid of these LeftJ
             .Include(x=>x.MrcBatchInfo_ME)  // reduce it as one if it's possible
             .Include(x=>x.MrcBatchInfo_SI)  // or any other optimization
             .Include(x=>x.MrcBatchInfo_SE)
             .Include(x=>x.MrcBatchInfo_AI)
             .Include(x=>x.MrcBatchInfo_AE)
             .Where(x=>x.CollId==collId)
             //.Select( ) in this part and its OK 
             .ToListAsync();

Thx for any comment.

CodePudding user response：

Include and Select serve different purposes. While Include results in JOIN in the queries, it is not required for EF to perform joins, instead it is used for Eager Loading related data when you want to load an entire entity graph. (An entity and it's related entities)

Select on the other hand is for handling a concept called Projection. In these cases you aren't necessarily returning entities, but rather telling EF to use it's entities and their relations to populate a desired output.

So for example, if the data you want is something that your method would return, we would create a POCO DTO object or ViewModel to hold that desired output data:

[Serializable]
public class MerchantDTO
{
    public int CollId { get; set; }
    public string MRC_INC_NAME { get; set; }
    public string MRC_EXC_NAME { get; set; }
    public string STR_INC_NAME { get; set; }
    public string STR_EXC_NAME { get; set; }
    public string MSTR_INC_NAME { get; set; }
    public string MSTR_EXC_NAME { get; set; }

}

From here we can build a Select clause to load a DTO from the merchant entity graph, and EF can work out the related data via navigation properties. The advantage of this is that the resulting SQL will only pull back the data from the relevant tables that it needs, rather than everything from the Merchant and related tables.

var result = await _context.Merchants
    .Where(x=>x.CollId==collId)
    .Select(x => new MerchantDTO
    {
        CollId = x.CollId,
        MRC_INC_NAME = x.MrcBatchInfo_MI.Name,
        MRC_EXE_NAME = x.MrcBatchInfo_ME.Name,
        STR_INC_NAME = x.MrcBatchInfo_SI.Name,
        STR_INC_NAME = x.MrcBatchInfo_SE.Name,
        MSTR_INC_NAME = x.MrcBatchInfo_AI.Name,
        MSTR_INC_NAME = x.MrcBatchInfo_AE.Name
    }).ToListAsync();

... using more appropriate naming convention and I guessed at the details like Name property to be returned. EF will still generate Joins and such behind the scenes so don't expect it to mimic an existing query, but for the most part performance wise the queries that EF generates are pretty good.

There may be complex cases that can be optimized more explicitly in which case an option is to leverage Views and mapping entities to the view rather than the base tables. (good for complex read operations)

CodePudding user response：

AND C.MRC_INC_LIST_ID = MI.COLL_MRC_BATCH_ID( )
AND C.MRC_EXC_LIST_ID = ME.COLL_MRC_BATCH_ID( )
AND C.STR_INC_LIST_ID = SI.COLL_MRC_BATCH_ID( )
AND C.STR_EXC_LIST_ID = SE.COLL_MRC_BATCH_ID( )
AND C.MSTR_INC_LIST_ID = AI.COLL_MRC_BATCH_ID( )
AND C.MSTR_EXC_LIST_ID = AE.COLL_MRC_BATCH_ID( )

Personally, I think your database design is backwards.

Rather than having a MERCHANT table with 6 foreign keys to the same table. You need a table with a FK to MERCHANT, and a type column to represent the 6 (or more) types. This would either be on the MRC_BATCH_INFO table, or on a new many-to-many join table, depending on how these two tables are related.