Entity Framework Conditional Left Join and Custom Column Select

Question

I have a stored procedure which results in lots of data. and also want to convert this to EF

unable to figure out how to join to the relavent tables when an attribute is present for the system. and also the column selection is very dynamic in nature,

I could take this sql and execute this directly and get things sorted that way but would miss but the grid in the front end wont be able to handle 600mb of data thrown from the database.

so need paging thought can do this better with EF.

for reference purpose I have the following sql below.

    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)

I have looked at the Leftjoin2 extn method but that is not helping much.

What is the best possible action to get this on to EF.

or EF itself is a wrong choise for this sort of problems?

score 0 · Answer 1 · answered Jan 14 '16 at 18:04

This is a bad fit for EF. If all you are only trying to add paging -- add your own paging functionality to the stored proc. You can do this by using ROW_NUMBER OVER what every you are sorting by, then use an an outer query to return the page of data you want, for example...

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

yes get that I have a version which is very similar to this and also takes in a filter as well as sort order. it got little bit ugly and complicated to maintain. thought I could do some thing better with EF. — bhushanvinay, Jan 14 '16 at 18:19
EF works with simple, if you want complicated stay in the db — dbugger, Jan 14 '16 at 18:20

joordan831 · Accepted Answer · 2016-01-14T21:35:56.470

You can do dynamic query generating and then in the end do Skip().Take(). Your model for custom object may look like this:

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

Then in the get method map data,

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);

there are plenty of information on paging using LINQ http://stackoverflow.com/questions/2380413/paging-with-linq-for-objects — joordan831, Jan 14 '16 at 18:32
No, sorry that's a new class you have to write. It will have all the columns the SQL returns. — joordan831, Jan 14 '16 at 18:38

Entity Framework Conditional Left Join and Custom Column Select

2 Answers2