0

I want to use a value lookup in linq to entities like this:

x => x.Prop1 * values[x.Prop2]

Getting an error because it cannot translate this into a store expression. I guess I could write the values to the db prior to this and use a foreign key lookup but is that the only way around this?

(Too many rows to do this efficiently in linq to objects)

UPDATE: I've added a table to do the look up from on the DB side with a linq query that now looks like this:

var result = anEntityRepository.GroupBySum(
x => x.Prop1 * x.NavigationProperty.Prop2,
           g => new { groupedProperty = g.Prop3 }
           );

But the linq produced is unnecessarily complex:

SELECT 
[Distinct1].[Prop3] AS [Prop3], 
[Distinct1].[C1] AS [C1], 
(SELECT 
    SUM([Filter4].[A1]) AS [A1]
    FROM ( SELECT 
         [Filter3].[Prop1] * [Filter3].[Prop2] AS [A1]
        FROM ( SELECT [Extent4].[Prop3] AS [Prop3], [Extent4].[Prop1] AS [Prop1], [Extent6].[Prop2] AS [Prop2]
            FROM   [dbo].[anEntity] AS [Extent4]
            LEFT OUTER JOIN [dbo].[NavigationProperty] AS [Extent6] ON [Extent4].[NavigationPropertyId] = [Extent6].[NavigationPropertyId])  AS [Filter3]
        WHERE [Distinct1].[Prop3] = [Filter3].[Prop3]
    )  AS [Filter4]) AS [C2]
FROM ( SELECT DISTINCT 
    [Filter1].[Prop3] AS [Prop3], 
    1 AS [C1]
    FROM ( SELECT [Extent1].[Prop3] AS [Prop3], [Extent3].[NavigationPropertyId] AS [NavigationPropertyId]
        FROM   [dbo].[anEntity] AS [Extent1]
      ))
    )  AS [Filter1]
 )  AS [Distinct1]

Would expect it to do a single select statement with a join on the navigation property. Would also like to select multiple properties but it adds another unnecessary select each time. Any ideas on how I can simplify the generated sql?

Willis
  • 161
  • 1
  • 12
  • Could you show your entire query, or at least the method in which you use this expression? – Sergey Kalinichenko Oct 13 '15 at 10:01
  • Possible duplicate of [linq to entity framework: use dictionary in query](http://stackoverflow.com/questions/7638859/linq-to-entity-framework-use-dictionary-in-query) – stuartd Oct 13 '15 at 10:01
  • Too many rows to do this efficiently in linq to objects – Willis Oct 13 '15 at 10:17
  • this is not easy, The rule of thumb when trying to use local collection (which cannot use Contains, ...) is you need to loop through all elements and concat or build up the expressions yourself. That means there will be some limit in the collection's count. Also doing like this is not always better than carefully processing the query partially locally. BTW you can try playing around with `LinqKit` library to see if it can help something. – King King Oct 13 '15 at 10:57
  • 1
    `(Too many rows to do this efficiently in linq to objects)` unless you are going to be doing filtering based on your lookup values, you are likely better off doing it in linq to objects. If you are going to be doing filtering, there are ways of achieving this (TVLs), or prepopulating the database with a lookup table and doing a join. – Robert McKee Oct 13 '15 at 19:04
  • 1
    Why do you care how complex the generated query is so long as it is performant enough for your needs? The SQL compiler will likely optimize away much of the visual complexity. – D Stanley Oct 14 '15 at 16:24
  • If it's doing multiple select statements when only one is needed isn't it going to be significantly slower than it needs to be? – Willis Oct 15 '15 at 10:17

0 Answers0