Check the existence of a record before inserting a new record

Question

I'm using the Ado.net Entity Framework for the first time and I need to check if this record exist before I insert it to the database. Preferably I'd search if AuthodSSID exists and not the key (AuthorID). I'm using VS2010, Framework 4. System.Data.Entity is 3.5.0.0.

I googled, but found no answer for this question.

PublishingCompanyEntities publishContext;
publishContext = new PublishingCompanyEntities();

private void createNew_Click(object sender, EventArgs e)
{
    Author newAuthor = new Author();
    newAuthor.FirstName = firstName.Text;
    newAuthor.LastName = lastName.Text;
    newAuthor.AuthodSSID = 20;
    newAuthor.AuthorID = 10
//Check if record exist here
    publishContext.AddToAuthor(newAuthor);//insert if does not exist

}

Old but still looks like a DUP; more info can be found at http://stackoverflow.com/questions/1802286/best-way-to-check-if-object-exists-in-entity-framework — Joel Peltonen, Apr 25 '13 at 06:47
Possible duplicate of [Best way to check if object exists in Entity Framework?](https://stackoverflow.com/questions/1802286/best-way-to-check-if-object-exists-in-entity-framework) — William Ardila, Dec 26 '17 at 14:00

score 22 · Accepted Answer · answered Feb 15 '12 at 03:31

22

The only way to check if a record exists is to query the record and see if anything comes back:

var existingAuthorCount = publishContext.Author.Count(a => a.AuthodSSID == 20);
if (existingAuthorCount == 0) 
{
    // Do your insert
}

answered Feb 15 '12 at 03:31

Jacob

77,566
24
149
228

1

Working, just had to figure out why didn't the .Count appear. Simple, using System.Linq; was missing on the top! Tkx!! – Jitesh Pramodray Feb 15 '12 at 03:45
1

From the wise advice of the infamous Eric Lippert: You have a jar entirely full of pennies. Someone asks you "are there any pennies in that jar?". **Do you count them and then compare the answer to zero, or do you see if there is at least one penny in the jar?** ([Citation](http://stackoverflow.com/questions/9194908/safely-checking-non-repeatable-ienumerables-for-emptiness#comment11574518_9194908)). The performance of this solution will depend on the number of rows contained in `Author`. I've updated my answer to limit the resulting query to only look for 1. – M.Babcock Feb 15 '12 at 04:15
6

Can you not use publishContext.Author.Any(a => a.AuthodSSID == 20) or is .Any() unavailable in EF? – Trevor Pilley Oct 29 '12 at 21:29
I would reiterate what Trevor said. I'm currently using .Any in what I think is the same situation. – mrshickadance May 06 '14 at 21:12
1

Yes, please use the answer from @M.Babcock if `Any` is supported by your version of EF. – Jacob May 07 '14 at 21:46

M.Babcock · Answer 2 · 2013-05-23T17:55:00.300

12

Something like this should work:

if (publishContext.Author.Select(a => a.AuthodSSID).Where(id => id == 20).Take(1) == null)
    // It doesn't exist
else
    // It does exist

Based on my (albeit fundamental) understanding this should produce a SQL statement equivalent to:

SELECT TOP(1) AutodSSID FROM Author WHERE AuthodSSID = 20;

Another simpler approach may be to use the Any extension method:

if (!publishContext.Author.Any(a => a.AuthodSSID == 20))
    // Put your insert logic here.

edited May 23 '13 at 17:55

answered Feb 15 '12 at 03:30

M.Babcock

18,753
6
54
84

This would work and is best if you want to update the author if it does exist. However, this will select all columns for the author, which you may not need. Doing a count as in my answer would be more efficient. – Jacob Feb 15 '12 at 03:32
@Jacob - A count would effectively be the same as including a select of a single column, but using `SingleOrDefault` I would think it would be doing a `TOP 1` where `COUNT(*)` still needs *all* of the records, or am I missing something? – M.Babcock Feb 15 '12 at 03:42
1

Good question. My concern with `TOP 1` is that all *columns* are still returned for that one row. `COUNT` could possibly require more table scanning on the database, but only an integer would be returned. Maybe the best choice is to do a `Where`, then a `Select` of the primary key, followed by a `Take(1)`. That should resolve both concerns. – Jacob Feb 15 '12 at 05:07
@Jacob - Sort of like my current answer? – M.Babcock Feb 15 '12 at 05:10
No. Your current answer returns all columns. – Jacob Feb 15 '12 at 05:13

Ryk · Answer 3 · 2012-10-29T21:17:25.613

4

I personally prefer this approach from a .NET point of view. It is cleaner and if you care about speed (in .NET), it is more efficient, however the SQL is not that flash;

private bool CheckIfEntityRecordExists(Entity e)
{
    var retVal = false;
    using (var db = new EntityContext())
    {
        retVal = db.AdviserClients.Any(a => a.Id == e.Id);
    }
    return retVal;
}

So for a efficient SQL statement, the following is the best:

private bool CheckIfEntityRecordExists(Entity e)
{
    var retVal = false;
    using (var db = new EntityContext())
    {
        retVal = db.AdviserClients.Count(a => a.Id == e.Id) > 0;
    }
    return retVal;
}

edited Oct 29 '12 at 21:17

answered Oct 21 '12 at 23:49

Ryk

3,072
5
27
32

3

though i had upvoted your answer but from performance perspective one should use db.context.Any() as db.contex.count() will iterate all the data while .Any will stop at 1st match only. – Anshul Nigam Apr 28 '15 at 13:40
Using Any is the least expensive method of using EF to check if a row exist in the DB. This is my standard way to go for all such checks. – Manuel.B May 05 '22 at 03:34

score 2 · Answer 4 · edited Aug 24 '22 at 04:33

2

This method is advised to only be used for migrations to seed data, not as an upsert method

There is so called "upsert" operation available in EF v5.0+

publishContext.Author.AddOrUpdate(x => x.Id, newAuthor)

AddOrUpdate can be found in the "System.Data.Entity.Migrations" namespace, so don't forget to add:

using System.Data.Entity.Migrations;

The AddOrUpdate operation is not atomic. But *if (existingAuthorCount == 0) {// Do your insert} isn't also.

edited Aug 24 '22 at 04:33

Remco Schrijver

29
5

answered Mar 30 '17 at 07:06

Alexander Khomenko

569
1
5
13

1

Whats up :DDDDDD – Jota.Toledo Mar 31 '18 at 20:21

gideon · Answer 5 · 2012-02-15T03:36:49.860

All you need to do is search (with linq) for an author with that ID.

The Where() method will return a collection of authors you only need one, so you use FirstOrDefault() which returns the first element or null if there is nothing. You could also use SinglOrDefault which throws an exception if there is more than one element in the list, or just returns that element.

Seems @Jacob has an excellent, more efficient approach!

var author = publishContext.Authors.Where
                               (a=>a.AuthodSSID == 10).FirstOrDefault();
if(author == null) //none exist
{//don't bother creating one unless you need to..
    Author newAuthor = new Author();
    newAuthor.FirstName = firstName.Text;
    newAuthor.LastName = lastName.Text;
    newAuthor.AuthodSSID = 20;
    newAuthor.AuthorID = 10
    publishContext.AddToAuthor(newAuthor);//insert if does not exist
}

Check the existence of a record before inserting a new record

5 Answers5

This method is advised to only be used for migrations to seed data, not as an upsert method

Linked