Entity Framework: equivalent of INSERT OR IGNORE

Question

I'm using a database as a kind of cache - the purpose is to store currently known "facts" (the details aren't that interesting). Crucially, however, I'd like to avoid inserting duplicate facts. To do so, the current data access code uses insert or ignore in many places.

I'd like to use the entity framework to benefit from it's manageability; I've had enough of writing boilerplate code for yet another on subtly different query, and so far the Entity Framework's support for queries looks great.

However, I can't really seem to find good support for adding new facts - the obvious way seems to be to simply construct new Entities in .NET, add them to the model, and save the changes to the database. However, this means that I need to check in advance whether a particular row already exists since if it does insertion is not permitted. In the best of times, that means extra roundtrips, but it's usually much worse: previously this code was highly parallel - the occasional duplicate insert wouldn't matter since the database would just ignore it - but now not only would I need to check whether a row exists beforehand, I'd need to do so in far smaller batches (which is typically many times slower due to transaction overhead) since just one already-existing row from a parallel insert will cause a rollback of the entire transaction.

So, does anyone know how to get similar behavior using the entity framework?

To be specific, I'd like to be able to perform several inserts and have the database simply ignore those inserts that are impossible - or more generally, to be able to express server-side conflict resolution code to avoid unnecessary roundtrips and transactions.

If it matters, I'm using SQLite.

Answer 1

This functionality can be implemented via stored procedures (which the entity framework can use) on DB's that support it (MSSQL, for instance). On SQLite, these aren't supported. However, a simple solution exists for sqlite as well, namely to use triggers such as the following:

CREATE TRIGGER IF NOT EXISTS MyIgnoreTrigger BEFORE INSERT ON TheTable
FOR EACH ROW BEGIN 
   INSERT OR IGNORE 
      INTO TheTable (col1, col2, col3) 
      VALUES (NEW.col1, NEW.col2, NEW.col3);
   select RAISE(IGNORE);
END;

Alternatively, the trigger could be set up to directly verify the constraint and only raise "ignore" when the constraint is violated.