Experiences in using LINQ to EF and LINQ to object

As we all know, LINQ can be used to query database objects (I am referring to the model object in Entity Framework) or ienumerable objects in memory.

There will be no problem when the two queries are separate, but when they are mixed together (generally using keywords to join connections), there will be more to note.

Scenario 1: Join (join) LINQ to entity

Let's take a look at this Code: (Note: The data code in the memory, that is, the data code in the LINQ to object is placed before the join, and the data code in the database is placed behind the join)

List<MyObject> objectList = new List<MyObject>();objectList.Add(new MyObject { Identity = 1, Name = "Jack", Age = 30 });objectList.Add(new MyObject { Identity = 2, Name = "Sam", Age = 28 });objectList.Add(new MyObject { Identity = 3, Name = "Lucy", Age = 23 });EntityRepository repository = new EntityRepository();DbSet<Entity> entitySet = repository.Context.Set<Entity>();var objectNames = (from ob in objectList                   join en in entitySet                   on ob.Identity equals en.SID                   select ob.Name).ToList();

Entity is a database table and has a bigint type field named Sid. The two fields are name and notes. In the code above, we put LINQ to object in front and LINQ to entity in join, so that compilation runs smoothly.

The actual statement for querying the database is:

SELECT [Extent1].[SID] AS [SID], [Extent1].[Name] AS [Name], [Extent1].[Notes] AS [Notes]FROM [dbo].[Entity] AS [Extent1]

Here is the whole table query. (You can open SQL Server 2008 R2 --> Performance Tools --> SQL Server Profiler to track this statement ).

If you change the preceding code to select en. Name, the entire table is still queried.

If you are designing a query for two database tables and finally a column in the select table, it is not possible to query the whole table, but to query a column separately; if you put the entity and object together, the whole table query will be involved. This is the first drawback of mixed use of the two.

Scenario 2: LINQ to entity join (join) LINQ to object

Data Code of the database before join: Before join, data code in the memory after join. The Code is as follows:

 var entityNames = (from en in entitySet                    join ob in objectList                    on en.SID equals ob.Identity                    select en.Name).ToList();

Okay, the compilation is successful, and an exception is thrown during the runtime.

Only primitive types ('such as int32, string, and guid ') are supported in this context

The constant value of "project name. myobject" cannot be created. This context only supports primitive types (such as int32, string, and guid ")"

It seems that when this operation is involved, the data in our memory cannot be non-primitive type. List <myobject> objectlist = new list <myobject> ();

Myobject must be int32, string, or GUID to run and pass the query. Instead of the whole table query, it is a separate query for the name column. You can try it.

Therefore, I would like to give you some suggestions:

When a hybrid query is performed between objects in the memory and objects in EF, if the objects in the memory are not of the primitive type, query one of the variables to be matched first, take it and perform a hybrid query with EF objects.

In this way, not only will there be no errors, but the efficiency is also high, and the code will be written in two sections, it is easy to clearly understand the meaning.

The above code is the best

IEnumerable<long> idList = objectList.Select(o => o.Identity);var entityNames = (from en in entitySet                   join id in idList                   on en.SID equals id                   select en.Name).ToList();

The database query statement is as follows:

SELECT [Extent1].[Name] AS [Name]FROM  [dbo].[Entity] AS [Extent1]INNER JOIN  (SELECT [UnionAll1].[C1] AS [C1]FROM  (SELECT cast(1 as bigint) AS [C1]FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]UNION ALLSELECT cast(2 as bigint) AS [C1]FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]UNION ALLSELECT cast(3 as bigint) AS [C1]FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2] ON  CAST( [Extent1].[SID] AS bigint) = [UnionAll2].[C1]

Although the operations are troublesome, only one name is found. Relatively speaking, it is better.

Case 3: LINQ to entities does not recognize the method 'int32 get_item (int32 )'

LINQ to entities does not recognize the method 'int32 get_item (int32) 'method, and this method cannot be translated into a store expression

LINQ to entities does not recognize the method "int64 get_item (int32)", so this method cannot be converted to a storage expression.

View the code

List<long> list = new List<long> { 1, 2, 3 };var entity = entitySet.Where(en => en.SID == list[1]).ToList();

In this case, the subscript operation of the array is placed in the LINQ expression, which is not allowed. However, contains is used, such as list. Contains (EN. Sid)

Or you can place the subscript operation in the LINQ statement.

Relational databases cannot identify SQL queries translated from expression trees containing subscripts. Why? Ask Microsoft engineers!

Case 4: Only parameterless constructors and initializers are supported in LINQ to entities

See the following code:

 EntityRepository repository = new EntityRepository(); DbSet<Entity> entitySet = repository.Context.Set<Entity>(); List<Tuple<long, string>> tuple = entitySet.Select(en => new Tuple<long, string>(en.SID, en.Name)).ToList();

The query is still for EF. Here, tuple is a tuples class. Multiple parameters can be used to construct a tuples class (or an anonymous class ). An exception is reported during running. The Chinese meaning is:

Only the no-parameter constructor and the Initial Value Setting items are supported. It seems that a constructor with parameters is used in LINQ, which is not supported by LINQ. Let's use the Anonymous class to try it out.

var tuple = entitySet.Select(en => new { en.SID, en.Name }).ToList();

Running properly. But in some cases, we have to use tuple to accept the two parameters so that the values can be passed between functions. What should we do? As a matter of fact, there is naturally a way to figure out the real reasons. Because after select

. Tolist () is used to access the database. In this case, tuple with constructor cannot survive. Therefore, this operation is only possible after the database is disconnected.

List<Tuple<long, string>> tuple = entitySet.Select(en => new { en.SID, en.Name })                                            .AsEnumerable()                                            .Select(en => new Tuple<long, string>(en.SID, en.Name)).ToList();

Entityset. Select (en => New {en. Sid, en. name}) returns the iqueryable type. After an asenumerable () is added, the ienumerable type is used to disconnect the database.

Of course, you can use tolist () or toarray!