0

I have some code, i want to make reusing of conditional, but i don't know how. Do i need dynamic IQueryable or something other? Can you help me make it or find some instruction for it?

   DateTime dtTo = DateTime.Now;
   DateTime notEndDt = new DateTime(2099, 1, 1);
   var result = from o in data
                select new
   {
       id,
       Group1Type1result =  o.group_id == 1 && o.type== 1 && (NeedActual ? o.dt_end == notEndDt : (o.dt_end == notEndDt || dtTo <= o.dt_end)) ? o.result : 0,
       Group1Type2result = o.group_id == 1 && o.type== 2 && (NeedActual ? o.dt_end == notEndDt : (o.dt_end == notEndDt || dtTo <= o.dt_end)) ? o.result : 0,
       Group2Type1result =  o.group_id == 2 && o.type== 1 && (NeedActual ? o.dt_end == notEndDt : (o.dt_end == notEndDt || dtTo <= o.dt_end)) ? o.result : 0,
       Group2Type2result = o.group_id == 2 && o.type== 2 && (NeedActual ? o.dt_end == notEndDt : (o.dt_end == notEndDt || dtTo <= o.dt_end)) ? o.result : 0
   };

I want to get smth as it:

   int GetResultForGroup(Model.Data o, int groupId, int type)
   {
      DateTime dtTo = DateTime.Now;
      DateTime notEndDt = new DateTime(2099, 1, 1);
      return o.group_id == groupId && o.type == type && (NeedActual ? o.dt_end == notEndDt : (o.dt_end == notEndDt || dtTo <= o.dt_end)) ? o.result : 0,
   }

   var result = from o in data
                select new
   {
       id,
       Group1Type1result =  GetResultForGroup(o, 1, 1),
       Group1Type2result = GetResultForGroup(o, 1, 2),
       Group2Type1result =  GetResultForGroup(o, 2, 1),
       Group2Type2result = GetResultForGroup(o, 2, 2)
   };

If i try to make it as in IEnumerable LINQ to Entities does not recognize the method 'Int32 GetResultForGroup(Model.Data, System.DateTime)' method, and this method cannot be translated into a store expression.

upd: I know that i can get everything from BD in IEnumerable and after it use my method, but can i do it in IQueryable? I have paging after query.

Константин Золин
  • 447
  • 6
  • 9
  • Linq to EF is limited to what the provider can translate to the underlying database engine's query language - eg. to SQL statements. Thus you either fetch everything to list and use Linq to Objects or create a proper view or stored procedure and map it to EF entity or reformulate your filter to what EF can translate. Unfortunately, I can't figure out what your final goal is. – ZorgoZ Nov 02 '19 at 05:43
  • You better add your comment as edit to your post. – ZorgoZ Nov 02 '19 at 05:44
  • Remember: EF is translating the expression tree to query, is not using the code itself, thus you can't use custom methods within those expressions. – ZorgoZ Nov 02 '19 at 05:54
  • @ZorgoZ "Unfortunately, I can't figure out what your final goal is.". I have more groups than in example (now 9 in future maybe more). And i need to update condition add more "&&" and "||". I want to get clean code making one condition and reuse it with params. I don't want make 9*2=18 copies of condition with changing groupId and typeId. Also i know about stored procedures, it can solve my problem, but i want know can i solve my problem using only IQueryable. – Константин Золин Nov 02 '19 at 06:00
  • @ZorgoZ "Remember: EF is translating the expression tree to query, is not using the code itself, thus you can't use custom methods within those expressions." I understand it, but my english not good, bacuse of it I used this example to clarify what I'm trying to do. Because of it i didn't ask why i get Exception :-). – Константин Золин Nov 02 '19 at 06:03
  • So you want to add some group "tags" to your rows based on some date values for further use. And you want to not repeat yourself. (If you will have more than 4 of those `Group1Type1result` properties anytime, this approach is not good.) Anyway, if you can use considerable filtering on EF side without the need for those tags (e.g. based on the date) and reduce the result to only the entities you need, then you can have those tags as method(s) in the model class. E.g.: > filter dbset > fetch result as list > process unconstrainted with Linq to Objects. Can this work for you? – ZorgoZ Nov 02 '19 at 06:40
  • @ZorgoZ "Can this work for you? " I cann't use it because after query i have paging. I can't order it without results of groups. – Константин Золин Nov 02 '19 at 06:55

0 Answers0