CODEWITHSUNDEEP
c#entity-framework-core
Ryan Taite
Ryan Taite

Reputation: 839

How to use a single query to check if a value exists in two tables

Using EF Core 2.2.2, I have these two simple queries with the goal of determining if a Color is in use in either the CollectionItem or PuzzleColor table:

var isACollectionItem = _applicationDbContext.CollectionItem.Any(collectionItem => collectionItem.ColorId == color.Id);
var isAPuzzleItem = _applicationDbContext.PuzzleColor.Any(puzzleColor => puzzleColor.ColorId == color.Id);
var eitherOr = 
        _applicationDbContext.CollectionItem.Any(collectionItem => collectionItem.ColorId == color.Id)
    ||  _applicationDbContext.PuzzleColor.Any(puzzleColor => puzzleColor.ColorId == color.Id);

The first two lines generate two separate queries that look like:

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [CollectionItem] AS [collectionItem]
        WHERE [collectionItem].[ColorId] = @__color_Id_0)
    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END

Putting them together with an || operator means it will run both in the event the first isn't true, otherwise it will only run the first query.

Is it possible to merge both checks into a single query with EF?

Upvotes: 1

Views: 171

Answers (1)

Ryan Taite
Ryan Taite

Reputation: 839

I was able to get a single query by using the Color table (that I did not include in my original post, my bad) as the starting point:

bool singleQuery = _applicationDbContext.Color
    .Any(myColor =>
            myColor.Id == _applicationDbContext.CollectionItem.FirstOrDefault(collectionItem => collectionItem.ColorId == color.Id).ColorId
        ||  myColor.Id == _applicationDbContext.PuzzleColor.FirstOrDefault(puzzleColor => puzzleColor.ColorId == color.Id).ColorId);

Which gets translated into:

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Color] AS [myColor]
        WHERE ([myColor].[Id] = (
            SELECT TOP(1) [collectionItem].[ColorId]
            FROM [CollectionItem] AS [collectionItem]
            WHERE [collectionItem].[ColorId] = @__color_Id_0
        )) OR ([myColor].[Id] = (
            SELECT TOP(1) [puzzleColor].[ColorId]
            FROM [ServedColor] AS [puzzleColor]
            WHERE ([puzzleColor].[Discriminator] = N'PuzzleColor') AND ([puzzleColor].[ColorId] = @__color_Id_1)
        )))
    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END

I imagine that a more performant query could be created by utilizing joins or unions though

Upvotes: 1

Related Questions