Queries with local collections are not supported

I am sending this articleArticleWhen you do not post it on the homepage. Complicated query of LINQ is a headache. I hope to communicate with experts in the garden, and I am afraid that this post will soon sink, so let's put it on the homepage. You are welcome to visit and guide. Pai_^

I. Events

A recent project requires the following query:

There are three tables: paper, Journal (magazine), and Journal subject category (magazinesubjectclass). The relationships are as follows: the relationship between papers and journals is one-to-many (one paper corresponds to one journal and one journal corresponds to multiple papers ), the relationship between a journal and its category is one-to-many (one journal corresponds to multiple categories ),

 

The required query is as follows:

A set of "subject categories" (String Array) is provided to query the papers published on the magazine that belongs to the set of "subject categories.

2. Event Response

My ideas are as follows:

1. Identify qualified journals by subject category
2. Find qualified papers based on magazines

Therefore, I wrote the following LINQ statement:

Code
Iqueryable < Magazine > Magazines = Database. Magazines. asqueryable ();
If (Paperqueryinformation. magazinequeryinformation. subjectclass ! =   Null   && Paperqueryinformation. magazinequeryinformation. subjectclass. Length ! =   0 )
{
Magazines = Database. magazinesubjectclasses
. Where (Q => Paperqueryinformation. magazinequeryinformation. subjectclass. Contains (Q. subjectclass ))
. Select (Q => Q. magazine)
. Distinct ();

Query = Query
. Where (Q => Magazines. Contains (Q. Magazine ));
}

Run the following error:

Queries with local collections are not supported

Why is this error? It turns out that this error is caused by the delayed execution of LINQ. I have learned the cause and made the following improvements:

Code
Ilist < Magazine > Magazines = Database. Magazines. tolist ();
If (Paperqueryinformation. magazinequeryinformation. subjectclass ! =   Null   && Paperqueryinformation. magazinequeryinformation. subjectclass. Length ! =   0 )
{
Magazines = Database. magazinesubjectclasses
. Where (Q => Paperqueryinformation. magazinequeryinformation. subjectclass. Contains (Q. subjectclass ))
. Select (Q => Q. magazine)
. Distinct ()
. Tolist ();
Query = Query
. Where (Q => Magazines. Contains (Q. Magazine ));
}

Run, the error disappears, but a new error occurs, as shown below:

Method 'boolean contains (srims. Business. Papers. Magazine) 'has no supported translation to SQL

The solution to this error is to convert the type to generic ienumerable, and use asenumerable <tsource> to return the parameter of type to generic ienumerable. By using the default generic query, you can convert a query to an SQL statement and run it on the server. However, this method cannot be converted to SQL when writing. The solution is to use generic ienumerable <t> to replace generic iqueryable <t>. You can call the asenumerable <tsource> operator to perform this operation.

The improved query statement is as follows:

Code
Ilist < Magazine > Magazines = Database. Magazines. tolist ();
If (Paperqueryinformation. magazinequeryinformation. subjectclass ! =   Null   && Paperqueryinformation. magazinequeryinformation. subjectclass. Length ! =   0 )
{
Magazines = Database. magazinesubjectclasses
. Where (Q => Paperqueryinformation. magazinequeryinformation. subjectclass. Contains (Q. subjectclass ))
. Select (Q => Q. magazine)
. Distinct ()
. Tolist ();
Query = Query
. Where (Q => Magazines. asenumerable ()
. Contains (Q. Magazine ));
}

Run, the result is correct, and the problem is solved.

Iii. Postscript

After continuous efforts, the problem was finally solved. In the complex query of LINQ to SQL, unexpected errors may occur during the delayed execution of LINQ and the translation of a LINQ statement into an SQL statement, this is one of the most common problems I have encountered. I have finally solved this problem today. I would like to share it with you and hope to give you a better chance. Pai_^