CODEWITHSUNDEEP
c#linqsubqueryentity-framework-core
Tyimi
Tyimi

Reputation: 31

Linq - Using a query as function in a select

When I'm calling a function in a select, I get this Error: A second operation started on this context before a previous operation completed.

public bool func2(int ID)
    {
        return (from t2 in _odc.table2
                where t2.ID == ID &&
                (t2.col1 > 0 ||
                t2.col2 != "" ||
                t2.col3 != "")
                select t2
               ).Any();
    }

public List<MyModel> func1()
    {

        return (from t1 in _odc.t1
                join t3 in _odc.t3 on t1.ID equals t3.ID
                where t1.col2 > 300
                where t1.col3 != 1
                where t1.col4 != 285
                where t1.col5 != 830
                where t1.col6 > 0
         select new MyModel
                {
                    ID = t1.ID,
                    isFunc2 = func2(t1.ID),
                }).ToList();
    }

Can I do it like this or do I have to call func2 in a foreach function? (Already tested it and it works with a foreach).

Upvotes: 0

Views: 764

Answers (1)

Harald Coppoolse
Harald Coppoolse

Reputation: 30464

You have to realize that your query implements IQueryable<...>.

An object that implements IQueryable has an Expression and a Provider. The Expression is a generic representation of what must be queried. The Provider knows who must execute the query and what language this executor uses.

When you start enumerating the sequence that the IQueryable represents (= when you call .ToList()) , the Expression is sent to the Provider. The Provider will translate the Expression into the language that the process that must execute the query understands (usually SQL) and sent it to the executing process.

The returned data is put into an object that implements IEnumerable<...>, and this object is enumerated.

The problem is, that the provider only knows how to translate fairly basic Expressions into SQL. It does not know how to translate Func2.

I don't see that you use any T3 item in your query. Is that a typing error?

Anyway, the easiest solution would be to put the code of Func2 into Func1:

(I'm more familiar with LINQ method syntax, than LINQ query syntax, but you'll get the gist)

see Enumerable.Join

var result = dbContext.T1
    // don't take all T1 items, but only the ones where:
    .Where(t1 => t1.col2 > 300
              && t1.col3 != 1
              && t1.col4 != 285
              && t1.col5 != 830
              && t1.col6 > 0
    // join the remaining t1s with table T3
    .Join dbContext.T3,
          t1 => t1.Id,               // from every T1 take the id
          t3 => t3.Id,               // from every T3 take the id

          // ResultSelector: take a t1 and a matching t3 to create one new object
          (t1, t3) => new MyModel
          {
              Id = t1.Id,

              // IsFunc2: true if Table2 has any element with Id equal to t1.Id and col values
              IsFunc2 = dbContext.Table2
                        .Where(t2 => t2.ID == t1.Id
                                  && (t2.col1 > 0 || t2.col2 != "" || t2.col3 != ""))
                        .Any();
    });

If you'll use Func2 often in various different Expressions, you could consider to transform Func2 such that it takes an IQueryable as input.

I created it as an extension function of IQueryable<Table2Row>. See extension methods demystified

static bool Func2(this IQueryable<Table2Row> rows, int id)
{
    return rows
        .Where(row => row.ID == id && (row.col1 > 0 || row.col2 != "" || row.col3 != ""))
        .Any();
}

Now you can use it in the ResultSelector of the Join:

(t1, t3) => new MyModel
{
    Id = t1.Id,
    IsFunc2 = dbContext.Table2.Func2(t1.Id),
});

Upvotes: 1

Related Questions