The "Go" LINQ to EF and LINQ to Object usage experience

As you know, LINQ can be used to query database objects (which I refer to as model objects in the Entity Framework), or to query IEnumerable objects in memory.

There is nothing wrong with both queries, but when mixed together (usually with keywords to join connections), there is more to note.

Scenario 1:linq to Object Connection (join) Linq to Entity

Let's take a look at this code first: (Note: LINQ code is to put in-memory data code, that is, LINQ to object in front of the join, the database data code behind the join)

[CSharp]View Plaincopy

2. list<myobject> objectList = new list<myobject> ();
4. Objectlist.add (new MyObject {Identity = 1, Name = "Jack", age = 30});
6. Objectlist.add (new MyObject {Identity = 2, Name = "Sam", age = 28});
8. Objectlist.add (new MyObject {Identity = 3, Name = "Lucy", age = 23});
12. Entityrepository repository = new Entityrepository ();
14. Dbset<entity> EntitySet = Repository. Context.set<entity> ();
18. var objectnames = (from OB in objectList
20. Join en in entitySet
22. On OB. Identity equals en. Sid
24. Select OB. Name). ToList ();

Entity is a database table, has a bigint type, named Sid Field, and the other two are name and notes. The code above is to put LINQ to object in front of the LINQ to Entity in the join, the compilation runs smoothly.

The statements for the actual query database are:

[SQL]View Plaincopy

1. SELECT
2. [Extent1]. [SID] as [SID],
3. [Extent1]. [name] as [name],
4. [Extent1]. [Notes] As [Notes]
5. from [dbo]. [Entity] As [Extent1]

Here is the whole table query. (You can open the SQL Server r2-->performance Tools-->sql server Profiler to track this statement.)

If you change the above code to select En. Name, which is still an entire table query.

If you are designing a query for two database tables and the last is a column in the Select table, it is not possible to query the whole table, but to query a column separately, and the entity and object together will involve an entire table query. This is the first disadvantage of mixed use of the two.

Scenario 2:linq to Entity Connection (join) Linq to Object

Data code for the database LINQ to entity before join, in-Memory Data code LINQ to Object after join. The code is as follows:

[CSharp]View Plaincopy

1. var entitynames = (from en in entitySet
2. Join OB in objectList
3. On en. SID equals OB. Identity
4. Select En. Name). ToList ();

Well, compile through, run-time throw exception.

Only Primitive types (' Such as Int32, string, and Guid ') is supported in this context

"Unable to create type for project name" in Chinese. Constant value of MyObject ". This context only supports primitive types ("such as Int32, String, and Guid") "

It seems that our in-memory data cannot be non-primitive types when it comes to this kind of operation. list<myobject> objectList = new list<myobject> ();

MyObject must be a int32, string, or GUID to run through, and not an entire table query, but a separate query for the name column. You can have a try.

So here's a little advice for you:

When a query is mixed between an object in memory and an object in EF, if the object in memory is not a primitive type, then one of the variables to participate in the match is queried first, and then the query is mixed with the EF object.

This will not only make mistakes, but also high efficiency, and the code will be written in two paragraphs, but also easy to see the meaning.

The above code is the best

[CSharp]View Plaincopy

1. ienumerable<long> idlist = objectlist.select (o = o.identity);
3. var entitynames = (from en in entitySet
4. Join ID in idlist
5. On en. SID equals ID
6. Select En. Name). ToList ();

The database query statements are as follows:

[SQL]View Plaincopy

2. SELECT
4. [Extent1]. [name] as [name]
6. from [dbo]. [Entity] As [Extent1]
8. INNER JOIN (SELECT
10. [UnionAll1]. [C1] As [C1]
12. From (SELECT
14. cast (1 as bigint) as [C1]
16. From ( SELECT 1 as X) as [SingleRowTable1]
18. UNION All
20. SELECT
22. cast (2 as bigint) as [C1]
24. From ( SELECT 1 as X) as [SingleRowTable2]) as [UnionAll1]
26. UNION All
28. SELECT
30. cast (3 as bigint) as [C1]
32. From ( SELECT 1 as X) as [SingleRowTable3]) as [UnionAll2] on CAST ([extent1].[ SID] as bigint) = [unionall2].[ C1]

Although the inside of the operation is a bit of trouble, but the last thing to query out only name one. Relatively speaking, it's better.

Case 3:linq to entities does not recognize the method ' Int32 get_item (Int32) '

Linq to Entities does isn't recognize the method ' Int32 get_item (Int32) ' method, and this method cannot is translated into a Store expression

Linq to Entities does not recognize the method "Int64 get_item (Int32)", so the method cannot be converted to a storage expression.

And look at the code

[CSharp]View Plaincopy

1. list<long> list = new list<long> {1, 2, 3};
2. 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 also not allowed. But with contains, the shape is like a list. Contains (en. SID)

Or it is possible to place subscript operations on LINQ statements.

Why does a relational database not recognize an SQL query translated into an expression tree that contains subscript operations? Maybe you should ask a Microsoft engineer!

Scenario 4:only parameterless constructors and initializers is supported in LINQ to Entities

Take a look at the following code:

[CSharp]View Plaincopy

1. Entityrepository repository = new Entityrepository ();
2. Dbset<entity> EntitySet = Repository. Context.set<entity> ();
3. list<tuple<Long, string>> Tuple = entityset.select (en = new tuple<long, string > (en. SID, en. Name)).  ToList ();

is still a query for EF. The tuple here is a tuple class, which can be used to create a tuple class (or anonymous Class) with multiple parameters. Run The Times exception, Chinese means:

LINQ to entities only supports parameterless constructors and initializers. It appears that the constructor with parameters is used in LINQ, which is not supported by LINQ. We use anonymous classes to try.

[CSharp]View Plaincopy

1. var tuple = entityset.select (en = new {en). SID, en. Name}).  ToList ();

is operating normally. But in some cases, we have to use tuple to accept these two parameters to facilitate the transfer of values between functions, what should be done? In fact, to figure out the real reason, naturally there is a way. Because we were after select

. ToList () To access the database, in this case, a tuple with constructors is naturally not alive. Therefore, this operation is only foolproof if you disconnect the database.

[CSharp]View Plaincopy

2. list<tuple<Long, string>> Tuple = entityset.select (en = new {en). SID, en. Name})
4. . AsEnumerable ()
6. . Select (en = new tuple<long, string> (en). SID, en. Name)).  ToList ();

Entityset.select (en = new {en). SID, en. Name}) Returns the IQueryable type, with a asenumerable () followed by the IEnumerable type, which disconnects the database.

Of course, with ToList () or ToArray () can also!

The "Go" LINQ to EF and LINQ to Object usage experience