How to clean & optimise code generated by WCF OData service?

I have an OData web service that is mostly OOTB in terms of how it accesses and returns data from the database.

T开发者_如何学Gohe problem we're seeing is that the code being generated is incredibly - and unnecessarily - complex and is causing huge delays when executing against the server.

For example:

SELECT 
[Extent1].[Id] AS [Id], 
N'ODATAModel.LatestFiguresView' AS [C1], 
N'EntityType,Name,RegistrationNumber,OfficeNumber,DateRegistered,...,Id' AS [C2], 
[Extent1].[EntityType] AS [EntityType], 
[Extent1].[Name] AS [Name], 
[Extent1].[RegistrationNumber] AS [RegistrationNumber], 
[Extent1].[OfficeNumber] AS [OfficeNumber], 
... Loads of other columns ...
,
N'' AS [C12]
FROM (SELECT 
      [LatestFiguresView].[Id] AS [Id], 
      [LatestFiguresView].[EntityType] AS [EntityType], 
      [LatestFiguresView].[Name] AS [Name], 
      [LatestFiguresView].[RegistrationNumber] AS [RegistrationNumber], 
      [LatestFiguresView].[OfficeNumber] AS [OfficeNumber], 
... Loads of those same columns ...

FROM [dbo].[LatestFiguresView] AS [LatestFiguresView]) AS [Extent1]
WHERE (N'Registered' = [Extent1].[RegistrationStatus]) 
    AND ((1 = [Extent1].[Reservation]) 
    OR (1 = (CASE WHEN ([Extent1].[Name] LIKE N'%White%') 
    THEN cast(1 as bit) 
    WHEN ( NOT ([Extent1].[Name] LIKE N'%White%')) 
    THEN cast(0 as bit) END)) 
    OR (1 = (CASE WHEN ([Extent1].[Sectors] LIKE '%Business%') 
    THEN cast(1 as bit) 
    WHEN ( NOT ([Extent1].[Sectors] LIKE '%Business%')) 
    THEN cast(0 as bit) END)) 
    OR ((1 = (CASE WHEN ... etc, etc ... END))))

As you can see, there are unnecessary nested selects and the WHERE clause contains loads of redundant checks on values (e.g. "CASE WHEN MyColumn LIKE '%Value%' THEN 1 WHEN NOT MyColumn LIKE '%Value%' THEN 0")

Surely there's a way to tidy this mess up before it hits the database?

When you select a large object graph from your database using Entity Framework you receive - as you're seeing - a huge swathe of data formed by joining a number of queried tables together. Then the 'magic' happens back in your application as this oversized result set is stripped and shredded back into entities.

Every column that you've mapped will be selected from the database. This is only natural - you can't select half of an entity. If you're selecting the LatestFiguresView then every column in that View will be in your select statement - and if you're retrieving Products on an Order then every mapped column on Product & Order will be in the result set.

Yes, Entity Framework always does SELECT [Column] FROM (SELECT [dbo].[Column] AS [Column]). I couldn't tell you why, but I'll guess that it either helps with code safety around some edge case where selecting directly from a table causes problems, or it allows the query generator to be more efficient. You're right that these are 'unnecessary' but I don't think they cost anything. I can run SELECT * FROM MyTable and SELECT * (SELECT * FROM MyTable) t in the same time.

Finally, the WHERE and CASE statements are determined by your query - without seeing it, or the full WHERE clause, it's hard to comment (note: I don't really want to see your query, I'm sure it's not pretty). If you have if x or y or (a & b) then things will get messy.

I don't normally worry about CASE statements as they are blindingly fast in SQL. I'd again assume that this is either a code-safety decision, or it allows for a more efficient (more generic?) query-generator.

I'd suggest you run this in Query Analyser to benchmark it and identify any problems, and then write your own SQL and do the same. There are plenty of cases where it's more efficient to write Stored Procs than to have Entity Framework compile your queries, but I'd be surprised if selecting directly from a View with a number of conditions would be one of those cases.