Entity Framework Core Linq query returning ids which do not exist in database

.net-core c# entity-framework-core linq linq-to-sql

Question

I wonder if there is an easy way using Linq to SQL with Entity Framework Core to query check if a given list of ids exist in the database and which returns the list of ids that do not exist.

The use case I come across this is if the user can do something with a list of object (represented through the list of their ids) I want to check if these ids exist or not.

Of course I could query all objects/object ids that exist in the database and cross check in a second step.

Just wondering if it would be possible in one step.

What I mean in code:

public class MyDbObject 
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public IActionResult DoSomethingWithObjects([FromQuery]List<int> ids} 
{
    List<int> idsThatDoNotExistInTheDb = DbContext.MyDbObject.Where(???)

    return NotFound("those ids do not exist: " + string.Join(", ", idsThatDoNotExist));
}
1
1
3/22/2018 1:51:17 PM

Accepted Answer

You can obtain the list of IDs that match, then remove them from the original list, like this:

var validIds = DbContext
    .MyDbObject
    .Where(obj => ids.Contains(obj.Id))
    .Select(obj => obj.Id);
var idsThatDoNotExistInTheDb = ids.Except(validIds);

This approach may be slow, though, so you may be better off doing it in a stored procedure that takes a table-valued parameter (how?)

Note: Pre-checks of this kind are not bullet-proof, because a change may happen between the moment when you validate IDs and the moment when you start the operation. It is better to structure your APIs in a way that it validates and then does whatever it needs to do right away. If validation fails, the API returns a list of errors.

1
3/22/2018 1:52:27 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow