EF Stripping Pits

1. Simple count generates unnecessary nesting

var xs = (from X in Dbcontext.db_api_operationallog where x.id<1  select 1). Count (); Result: SELECT [GroupBy1]. [A1] As [C1]from (SELECT     COUNT (1) as [A1] from    [dbo].[ Db_api_operationallog] as [Extent1]    WHERE [extent1].[ ID] < 1) as  [GroupBy1]

2, query some columns when the entire model query

    Context.db_API_Operationallog.FirstOrDefault (p = p.id = = PostID). Hits;    Or:    context.db_API_Operationallog.Find (PostID). Hits;

Will find the entire post table data and then the hits in the memory.

Optimization:

    Context.db_API_Operationallog.Where (p = p.id = = PostID). Select (p = = p.hits). FirstOrDefault ();

3, do not easily load all the data into memory

Sometimes an careless query in the SQL build inside Add a ToList (), ToArray (), such as the real execution of the query, so in the development environment often table data is relatively small, the program runs relatively fast, but one to the online environment data volume is large, there will be a memory full problem , the problem is relatively covert, so be careful when developing.

4, IQueryable, ienumerableienumerable execution where first walk memory, walk memory query

    Public ienumerable<db_api_operationallog> getallpost ()    {            return context. Post;    }    int id = +;    var log = Getallpost (). Where (s = = S.id <id). ToList ();

Information captured by SQL Server Profiler

SELECT [Extent1]. [ID] as [id], [Extent1]. [UID] As [UID], [Extent1]. [Types] As [types], [Extent1]. [Events] As [events], [Extent1]. [More] As [more], [Extent1]. [Money] As [money], [Extent1]. [Lastmoney] As [Lastmoney], [Extent1]. [Nowmoney] As [Nowmoney], [Extent1]. [Bak] As [Bak], [Extent1]. [Times] As [Times]from [dbo]. [Db_api_operationallog] As [Extent1]

Change the IEnumerable above into IQueryable.

exec sp_executesql N ' SELECT [Extent1]. [ID] as [id], [Extent1]. [UID] As [UID], [Extent1]. [Types] As [types], [Extent1]. [Events] As [events], [Extent1]. [More] As [more], [Extent1]. [Money] As [money], [Extent1]. [Lastmoney] As [Lastmoney], [Extent1]. [Nowmoney] As [Nowmoney], [Extent1]. [Bak] As [Bak], [Extent1]. [Times] As [Times]from [dbo]. [Db_api_operationallog] As [Extent1]where [Extent1]. [ID] < @p__linq__0 ', N ' @p__linq__0 int ', @p__linq__0 =2000

This pit is more of the same and more discreet.

5. Use NoTracking to reduce the state overhead

  DbContext.db_API_Operationallog.Where (s = = S.id < ID). Asnotracking (). ToList ();

Summarize

You always have to stop when you write EF. Use Sql Server Profiler to hone your code

EF Stripping Pits