How to project properties depending on conditions with LINQ and Entity Framework?

Question

I would like to declare variable of class property for LINQ List. I have about 100 properties in Database and i would like to change output data depends on user choice. For example if user chose "A" i would like to get "A" values from EntityFramework.

Here is my sample code:

List<ReadTime> report = bWDiagnosticEntities2.CA190STEST
   .Where(x => (x.Date > StartDate) && (x.Date <= EndDate))
   .Select(x => new ReadTime 
     { 
         Date = x.Date,
         kody5 = x.kodyBledow5NetVarA 
     })
   .OrderBy(x => x.Date)
   .ToList();

I would like to change:

kody5 = x.kodyBledow5NetVarA

to

kody5 = myVariable

Where myVariable depends on model sent from User.

Let me know if I understood your problem correctly: you would like to initialize the `kody5` field with one or another property of `x`, depending on a value of a third property of `x`. For instance: `new ReadTime { kody5 = x.SomeCondition ? x.OneProperty : x.AnotherProperty }`. Is that correct ? — kall2sollies, Mar 31 '22 at 08:06
@kall2sollies Yes, I would like to initalize the `kod5` field with one of another property of `x` but it will depends on Model gets from User. It would be like 100 properties, all are ints. Then i would like to convert int to BitArray and would like to send one bit to View. My idea is make a List with Object where would be: ParName from User, Name of Property and number of bit. — Wiktor Oleś, Mar 31 '22 at 08:09

kall2sollies · Accepted Answer · 2022-03-31T09:18:14.157

0

I think the simplest approach would be to split your query and projection parts. Query will be run by EF on database side, and retrieve all the fields.

Then you do your projection in-memory, and inject the property based on user's choice (the user is required to pick an existing property from a list).

One important thing to remember in an EF query, is that when you call ToList(), you will perform the actual database query. Then all the further statements will be performed in memory.

// This will perform an EF query, filtered by the where predicate
var reportEntities = yourContext.YourEntityModelDbSet
     // save resources on EF context if you don't need to update entities later
    .AsNoTracking()
    // Filter as much as you can in this predicate, for it will
    // be translated into SQL by EF
    .Where(x => x.Date > StartDate && x.Date <= EndDate)
    // Calling ToList will retrieve all entites matching the where predicate
    // including all their fields (take care of data size)
    .ToList();
    
// Then, use reflection to retrieve the data
// according to user's choice
var userChoice = GetUserInput(); // will return the property name as a string

var report = reportEntities
    .Select(x => new ReadTime 
     { 
         Date = x.Date,
         // Using reflection to get the value of a property
         // Given its name.
         Kody5 = x.GetType().GetProperty(userChoice)?.GetValue(x, null)
     })
     .ToList();

The ?. operator swallows any exception due to a non-existing property choice. It guarantees you won't get a runtime exception, but you'll never know if the property choice is wrong.

If the data volume is an issue (and it might, since you told you have like a hundred fields), perform a first Select() before the ToList(), where you will project strictly just the fields you will need, in any case.

edited Mar 31 '22 at 09:18

answered Mar 31 '22 at 08:18

kall2sollies

1,429
2
17
33

The problem is no to check Value of `x.SomeProperty`. My problem is to change the name o property i would pick from Db. For Example User is picking `ParA` so i would get `x.SomePropertyA` if user is picking B i would like to get `x.SomePropertyB` and `x.SomeProperties` could be like 100. It depends on what User would pick. Of course i could do it on `ifs` but it's pointless to make 100 ifs... – Wiktor Oleś Mar 31 '22 at 08:24
Ok so another approach on the last `Select` part would be using Reflection depending on user's choice. If you can make sure your user will select an existing property, use Reflection to project the property he chose to your ReadTime view model. – kall2sollies Mar 31 '22 at 08:46
I would have sure that user will select existing property, becuase I would make SelectList with these properties. – Wiktor Oleś Mar 31 '22 at 08:51
Could you make new answer with sample how should looks Select with Reflection? – Wiktor Oleś Mar 31 '22 at 09:04
I will edit my answer. EDIT: I just did. – kall2sollies Mar 31 '22 at 09:14
1

Yea, it' works :) Could I do it with one list? `reportEntities ` has 200 columns :D I won't take all of it to short executing time. – Wiktor Oleś Mar 31 '22 at 09:33
Well, if your user is given the ability to select any of these 200, with my approach, you have to retrieve them all. It the user is required to select only a restricted subset of these columns, the perform a first `Select` before the `ToList`, where you target only this subset of fields, to spare on data size. – kall2sollies Mar 31 '22 at 09:45
A totally different approach would be the good old way, bypassing EF automapping by generating a raw SQL query with a string builder. Even with this approach, you can still use EF to perform that raw query, and map it's results to entities or view models. – kall2sollies Mar 31 '22 at 09:47
` var ReportEntities = bWDiagnosticEntities2.CA190STEST.Where(x => x.Date > StartDate && x.Date <= EndDate).Select(x => new ReadTime { Date = x.Date, kody5 = (int)(x.GetType().GetProperty(userChoice)?.GetValue(x, null)) }).OrderBy(x => x.Date).ToList()` and this is not working. – Wiktor Oleś Mar 31 '22 at 09:51
1

Please provide more info than 'not working'! – kall2sollies Mar 31 '22 at 12:20
Error nr `CS8072` says that Linq coudn't have null. When I removed operator `?` Visual Studio says: `LINQ to Entities does not recognize the method 'System.Object GetValue(System.Object, System.Object[])' method, and this method cannot be translated into a store expression.` So Is not possible to use Reflection in EF? – Wiktor Oleś Apr 01 '22 at 06:35
I think you missed a call to `ToList` before selecting fields with reflection. Read my answer: everything before ToList is supposed to be converted to SQL. Of course, reflection can't be translated. Put a ToList just before the Select. – kall2sollies Apr 01 '22 at 13:30
Yes, you are right. I just miss understood. Now is working fine, thank you for your help. Could I use Reflection to change column name in EF? – Wiktor Oleś Apr 04 '22 at 05:10

How to project properties depending on conditions with LINQ and Entity Framework?

1 Answers1