Why Does My LINQ Query Fail When Joining with a Non-Primitive Type in the SELECT Clause?

Cannot create constant values ​​for non-basic or enum types

Problem Description

Exception during query: "Cannot create a constant value of type API.Models.PersonProtocol. Only primitive or enumerated types are supported in this context."

Root Cause

The query attempted to use a LINQ JOIN within the SELECT clause of another JOIN. However, this approach fails because ppCombined (an IEnumerable object of PersonProtocolType) cannot be used as a constant value type.

The specific problem is that the ppCombined collection represents data in memory, while the query is executed against the data in the database. The two sets of data cannot be combined.

Solution

To resolve this issue, extract the filtered items from ppCombined in memory after using the intermediate results to retrieve other properties from the database.

Modified query

To replace the original query, you can use the following modified method:

<code class="language-csharp">var persons = db.Favorites
    .Where(f => f.userId == userId)
    .Join(db.Person, f => f.personId, p => p.personId, (f, p) =>
        new // 匿名对象
        {
            personId = p.personId,
            addressId = p.addressId,   
            favoriteId = f.favoriteId,
        })
    .AsEnumerable() // 数据库查询在此处结束，其余部分是内存中的查询
    .Select(x =>
        new PersonDTO
        {
            personId = x.personId,
            addressId = x.addressId,   
            favoriteId = x.favoriteId,
            personProtocol = ppCombined
                .Where(p => p.personId == x.personId)
                .Select(p => new PersonProtocol
                {
                    personProtocolId = p.personProtocolId,
                    activateDt = p.activateDt,
                    personId = p.personId
                })
                .ToList()
        });</code>

The

AsEnumerable() method ensures that the database query completes before continuing with the in-memory query. This allows the ppCombined collection to be filtered and projected in memory after retrieving the database data.

The above is the detailed content of Why Does My LINQ Query Fail When Joining with a Non-Primitive Type in the SELECT Clause?. For more information, please follow other related articles on the PHP Chinese website!