实体框架条件左联接和自定义列选择

我有一个存储过程，它会产生大量数据。 并且还想将其转换为EF

当系统存在属性时，无法弄清楚如何联接到相关表。 而且列的选择本质上是非常动态的，

我可以采用这个 sql 并直接执行它并以这种方式对事情进行排序，但会错过，但前端的网格将无法处理从数据库抛出的 600mb 数据。

所以需要分页思想可以用EF更好地做到这一点。

作为参考目的，我在下面有以下 sql。

    Declare @SQL varchar(max);
Declare @SelectColumns VARCHAR(MAX)

SELECT DISTINCT @SelectColumns= STUFF((SELECT  ',''' + [PrimaryDataSource] + ''' Golden'
                     + ISNULL(CASE WHEN System1     IS NOT NULL THEN ', System1.'   + QUOTENAME([System1]) + ' System1'     END, '') 
                     + ISNULL(CASE WHEN System2 IS NOT NULL THEN ', System2.'   + QUOTENAME([System2]) + ' System2' END, '') 
                     + ISNULL(CASE WHEN [System3]   IS NOT NULL THEN ', System3.'   + QUOTENAME([System3])+ ' System3'      END, '') 
                     + ISNULL(CASE WHEN System4 IS NOT NULL THEN ', System4.' + QUOTENAME(System4)+ ' System4'    END, '') 
                     + ISNULL(CASE WHEN System5     IS NOT NULL THEN ', System5.'   + QUOTENAME(System5)+ ' System5'        END, '') 
                     + ISNULL(CASE WHEN System6     IS NOT NULL THEN ', System6.'   + QUOTENAME(System6)+ ' System6'        END, '') 
                     FROM [dbo].[TBL_Mapping]
where Attribute =@attributeName
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'')

SET     @SQL = '
        SELECT distinct 
                m.ID MappingID, 
                m.KeyValueUniqueKey, 
                m.ValueKeyUniqueKey, 
        ' + @SelectColumns + '
        FROM [dbo].[TBL_Mapping] M ' 
IF CHARINDEX('System1.',@SelectColumns) > 0
BEGIN
SET @SQL =  @SQL +  
            ' 
                LEFT OUTER JOIN dbo.VW_System1_ALL System1 ON 
                    System1.System1ID=M.System1ID '
END
IF CHARINDEX('System2.',@SelectColumns) > 0
BEGIN
        SET @SQL =  @SQL +  
            ' 
                LEFT OUTER JOIN dbo.TBL_System2 System2 ON 
                M.System2ID= System2.System2ID '
END
IF CHARINDEX('System4.',@SelectColumns) > 0
BEGIN
        SET @SQL =  @SQL + ' 
                LEFT OUTER JOIN DBO.tbl_System4 System4 ON 
                    System4.Key1 = M.KeyValueUniqueKey  AND 
                    System4.Value1 = ValueKeyUniqueKey '
END 
IF CHARINDEX('System5.',@SelectColumns) > 0
BEGIN
        SET @SQL =  @SQL + ' 
                LEFT OUTER JOIN DBO.tbl_System5 System5 ON 
                    System5.System5Id = M.System5Id'
END
IF CHARINDEX('System6.',@SelectColumns) > 0
BEGIN
        SET @SQL =  @SQL + ' 
                LEFT OUTER JOIN dbo.tbl_system6 System6 ON  
                    System6.System6Id = M.System6Id'
END
IF CHARINDEX('System3.',@SelectColumns) > 0
BEGIN
        SET @SQL =  @SQL + ' 
                LEFT OUTER JOIN [dbo].[TBL_System3] System3 ON 
                    System3.System3Id = M.System3Id'
END
    SET @SQL =  @SQL + ' 
        WHERE m.version=0 and isActive=1
        ORDER by m.ID'
    print @SQL  
    exec (@SQL)

我已经看过 Leftjoin2 extn 方法，但这并没有多大帮助。

将其引入 EF 的最佳可能操作是什么。

还是EF本身是此类问题的错误选择？

class MappingData
{
  //not sure what the data types are.
  int MappingId;
  int KeyValueUniqueKey;
  int ValueKeyUniqueKey;
  string System1;
  string System2;
  ...
  string System6;
}

IQueryable<MappingData> sql = db.TBL_Mapping
                              .Select(m => new MappingData {
                                                 MappingId = ID,
                                                 KeyValueUniqueKey = KeyValueUniqueKey,
                                                 ValueKeyUniqueKey = ValueKeyUniqueKey,
                                                 //leave other columns out
                                                 //they will be filled in 
                                                 //dynamically
                                     })
                              .Distinct();//get distinct
//--------------------
//REPEAT START
bool HasSystem1 = db.TBL_Mapping.Any(m => m.System1 != null);
//left outer join with System1 if it has it in the TBL_Mapping
if (HasSystem1)
{
    sql = 
         from m in sql
         join s1 in db.VW_System1_ALL
         on m.System1ID equals s1.System1ID into stemp
         from st in stemp.DefaultIfEmpty()
         select new { MappingId = st.Id, 
                      KeyValueUniqueKey = st.KeyValueUniqueKey, 
                      ValueKeyUniqueKey = st.ValueKeyUniqueKey, 
                      System1 = st.System1 }; //SystemX column.
}    
//REPEAT END
//--------------------
// repeat the above for System2 thru System6
//And in the end do paging.
var result = sql
                .Skip(currentPageNumber * numberOfObjectsInPage)
                .Take(numberOfObjectsInPage);

CREATE PROCEDURE [dbo].[PagedSomething]
    @pageSize int,
    @pageNum int  -- assume pages are 0-based
AS
BEGIN
    -- outer query does the paging in its where clause, 
    -- returning the selected "pages" from the raw results of the inner query
    SELECT RawResults.SomethingId
    FROM 
        -- inner query where you make your basic data
        (SELECT 
            s.SomethingId
           , ROW_NUMBER() OVER(ORDER BY s.SomethingId) RowID
            FROM Somethings s) RawResults
     WHERE RowID >= @pageNum * @pageSize + 1
     AND RowID < (@pageNum + 1) * @pageSize + 1
END