LINQ to SQL: how to select specific fields conditionally into DTOs (performance)

Question

LINQ to SQL is great however it backfires in performance in some cases. And with my case it backfires bigtime.

I have a StudentServiceDm that maps to a StudentService Table with 40 some fields. I have 10-20 millions of services in the table. The requirement from the client is that they need to export all services into excel from the application, while selecting only the fields they want at the time.

From my understanding, if i do

IQueryable<StudentServiceDm> serviceQuery = GetListQuery();
List<StudentServiceDto> dtos = serviceQuery.Select(m => new StudentServiceDto
{
    Id = m.Id,
    Name = m.Name,
}).ToList();

Then this translates into the SQL that selects only the fields I need

SELECT Id, Name FROM StudentService

Which saves performance by alot by decreasing the number of fields that need to be selected.

But in my case I need to allow the user to conditionally select fields like this, so I do this:

IQueryable<StudentServiceDm> serviceQuery = GetListQuery();

List<StudentServiceDto> dtos = serviceQuery.Select(m => new StudentServiceDto
            {
                Id = (options.ShowId) ? m.Id : null,
                Name = (options.ShowName) ? m.Name,
                StudentFirstName = (options.ShowFirstName) ? m.Student.FirstName : null,
                StudentLastName = (options.ShowLastName) ? m.Student.LastName : null,
                StudentServiceType = (options.ShowType) ? m.StudentServiceType.Name : null,
                StudentServiceSubType = (options.ShowSubType) ? m.StudentServiceSubType.Name : null,
                Date = (options.ShowDate) ? m.Date : null,
                // alot more assignments .....
            }).ToList();

However, this translates into the SQL query to something like this (according to SQL Profiler:

SELECT 
    CASE WHEN (@p__linq__1 = 1) THEN [Project2].[Date] END AS [C1], 
    CASE WHEN (@p__linq__2 = 1) THEN [Project2].[Name] END AS [C2], 
    CASE WHEN (@p__linq__3 = 1) THEN [Project2].[StudentServiceTypeId] END AS [C3], 
    CASE WHEN (@p__linq__4 = 1) THEN [Project2].[StudentServiceSubTypeId] END AS [C4], 
    CASE WHEN (@p__linq__5 = 1) THEN [Project2].[Date] END AS [C5], 
    // ..... more fields
    FROM * //

where it puts a condition on every field to check if it should retrieve ... and actually this query's performance is almost the same as if all fields were selected.

What I want to achieve is, if the user only chooses to select Name and Date, then the translated SQL should only be

SELECT Name, Date FROM StudentService

After some performance analysis, the performance difference between these 2 sets of statement is about 3-4 times.

Any expert opinion?

You could try adding an `if` for every possible column you want to be able to select. It won't be pretty but it should give you the query you want. — oakgun, May 08 '17 at 06:55
I already added IF, if you see the ternery operator for each subselect. However that produced the above SQL which is not good for performance if you can see. — andyh0316, May 08 '17 at 07:14
why would you need `SQL` translated string? why not to do all in `linq` and make an `excel` output? — Inside Man, May 08 '17 at 08:13
@Nofuzy when you use linq to retrieve data from SQL it translates into SQL, i'm trying to optimize that translation. — andyh0316, May 08 '17 at 09:55
@andyh0316 why the SQL translation is important for you? I think it is better to ask your question in another way. `Fastest way getting Data from Linq and Saving them into Excel`, you should not engage yourself with SQL translation while you are using Linq — Inside Man, May 08 '17 at 11:23
@Nofuzy Blindly trying random things without knowledge how things work is usually very inefficient way to work. — Antonín Lejsek, May 08 '17 at 15:33
@Nofuzy because I asked a very specific question about LINQ to SQL performance: How to select only the the fields the user needs? I didn't have to mention about export to Excel because that's not part of the problem. All excel library expects is a list of objects then it will work from there. It is irrelevant. In LINQ to SQL, i assume when people run reports, they sometimes check the produced query in SQL Profiler to see how to best optimize the LINQ. — andyh0316, May 08 '17 at 15:42
Take a look at http://stackoverflow.com/questions/43822899/adding-to-lambda-expression-and-work-with-entity-framework, the current answer can give you the idea. — Ivan Stoev, May 08 '17 at 23:57

score 3 · Answer 1 · edited May 23 '17 at 12:02

You can create Linq expressions at runtime that exactly correspond to what you'd type in inside a .Select() statement, it's just not very straightforward. I never did it for Linq to SQL specifically but I don't see why it wouldn't work. Something like How do I dynamically create an Expression<Func<MyClass, bool>> predicate? but of course I'd recommend reading the docs first to get familiar with expressions.

score 1 · Answer 2 · answered May 08 '17 at 06:47

1

The only way to do what you are interested in doing is to create lots of different Select() sections, each corresponding to a different combination of fields. However, you will need to create an awful lot of different combinations (N!, where N is the number of fields), so it is not really practical or maintainable.

There is no other way to do this using L2S.

If performance is important for you in this aspect, I suggest creating the sql directly and using something like Dapper to retrieve the data. Then you can easily control exactly the fields that you want to include.

answered May 08 '17 at 06:47

Yaakov Ellis

40,752
27
129
174

Thanks for the comment. I would not like to use SQL because just in the GetListQuery function, there's alot of LINQ to filter data based on users and it in modularized. If I did it in SQL i could not inherit and have to rewrite everything and it is unmaintainable. There is really no way to achieve what I want? In that case I will just have to stick with the poor performance. – andyh0316 May 08 '17 at 06:51
I agree.. the best way to get performance is to write some SQL which gets SQL Server to write directly into an Excel file... avoiding Linq-to-SQL completely. Something like this: http://stackoverflow.com/a/7390928/391605 – Mike Gledhill May 08 '17 at 06:56
@andyh0316 alternatively, you can make some conditional `selects` just for the most common use cases, and use the general (all fields) approach for the lesser-used cases – Yaakov Ellis May 08 '17 at 07:17

LINQ to SQL: how to select specific fields conditionally into DTOs (performance)

2 Answers2