ASP.NET LINQ query DbSet.Join() does not work-CodePudding

I try to run this code here, in ASP.NET Entity Framework 7. The target is to have the most efficient solution here which should be a JOIN if the database.

public async Task<List<Building>> GetAllAsync(string commaSeparatedBuildingIDs)
    {
        var buildingRefIDs = commaSeparatedBuildingIDs.Split(",").ToList();

            return await semiSoftDbContext.Buildings
            .Join(buildingRefIDs, building => building.ReferenceId, refID => refID, (building, id) => building)
            .ToListAsync();

    }

I get the following error:

System.InvalidOperationException: The LINQ expression 'DbSet() .Join( inner: __p_0, outerKeySelector: building => building.ReferenceId, innerKeySelector: refID => refID, resultSelector: (building, id) => building)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.`

I already have tried several variations of join and read through the internet, but it says that ASP.Net should work with Join().

CodePudding user response：

You should not join here (assuming you are using Entity Framework - AFAIK it does not handle quite a lot of operations with local collections, like joins), use Where with Contains:

return await semiSoftDbContext.Buildings
   .Where(b => buildingRefIDs.Contains(b.ReferenceId))
   .ToListAsync();