Entity Framework throws exception when using Find-method

Question

I have a simple web application which allows an user to upload 2 .csv-files containing certain data. To persist the data I use the Entity Framework in two different Import-methods.

First Import-method

    public void ImportOne(string path)
    {
        StreamReader sr = new StreamReader(path);

        using (var db = new ContextEv("RndContext"))
        {
            db.Database.ExecuteSqlCommand("DELETE FROM TableA");
            db.Database.ExecuteSqlCommand("DELETE FROM TableB");

            while (!sr.EndOfStream)
            {
                string[] data = sr.ReadLine().Split(';');
                string houseId = data[0];

                    House house = new House()
                    {
                        HouseId = houseId,
                    };

                    House dummy = db.Houses.Find(houseId);

                    if (!dummy.HouseId.Equals(house.HouseId))
                    {
                        db.Houses.Add(house);
                    }
                }
            }
        }
    }

This line fails: House dummy = db.Houses.Find(houseId); with the following exception:

The type of one of the primary key values did not match the type defined in the entity. See inner exception for details.\r\nParametername: keyValues

ErrorContext of InnerException:

keyword 'AS', line 1, column 22

ErrorDescription of InnerException:

The query syntax is not valid.

Alright, I checked if really the type is the problem here. However I haven't found anything wrong.

The "funny" thing about it is, that I use the same Find-method in another Import-method and it works without any exception!

using (var db = new ContextEv("RndContext"))
            {
                db.Database.ExecuteSqlCommand("DELETE FROM TableC");
                db.Database.ExecuteSqlCommand("DELETE FROM TableD");

            StreamReader sr = new StreamReader(path);

            while (!sr.EndOfStream)
            {
                string[] data = sr.ReadLine().Split(';');
                string houseId = data[5];

                    House house = db.Houses.Find(houseId);

                    ...
                    ...
                    db.SaveChanges();
                }
            }

I wasn't sure which code is really needed for you to answer my question but I'd be very happy to post more if someone asks for a particular code.

UPDATE 1 ANSWER TO user89861

'db.Houses.ToList().Find(h => h.HouseId == houseId)' threw an exception of type 'System.NullReferenceException'

" bei System.Data.Entity.Internal.Linq.InternalQuery1.GetEnumerator()\r\n bei System.Data.Entity.Internal.Linq.InternalSet1.GetEnumerator()\r\n bei System.Data.Entity.Infrastructure.DbQuery1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator()\r\n bei System.Collections.Generic.List1..ctor(IEnumerable1 collection)\r\n bei System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)"

Because `Find()` accepts `object key` as the parameter, you can pass anything to it, but first you have to make sure the type of the primary-key is indeed of the same type as your argument. Have you tried converting `houseId` to `int` first (assuming your primary-key is an `int`)? — haim770, Nov 16 '15 at 22:18
HouseId is nvarchar(128) and from the .csv a string is returned. So I think the data type is correct. As said, in one method it works in the other it doesn't.. and it's the same line of code. — David, Nov 16 '15 at 22:22
@David but does it have an actual value when you step through the code? it could be null/empty — Omer Bokhari, Nov 16 '15 at 22:23
@CodeNotFound I cannot find from which file this is in the exception. It just says: System.Exception {System.Data.Entity.Core.EntitySqlException} — David, Nov 16 '15 at 22:28
Anyway just try this => `db.Houses.Find(int.Parse(houseId))` — CodeNotFound, Nov 16 '15 at 22:46
@CodeNotFound Doesn't work.. as the primary key is a string. — David, Nov 16 '15 at 23:01
string in database => varchar, char, nvarchar ? Give us some helpful data to help you. Maybe a capture of "House" table database — CodeNotFound, Nov 16 '15 at 23:04

score 0 · Answer 1 · answered Nov 17 '15 at 02:28

0

Find() returns null if result is not found, so you should validate the variable house before accesssing its members:

House house = db.Houses.Find(obj => {return obj.HouseId == houseId;});
if (house == null) continue; //go to next iteration

answered Nov 17 '15 at 02:28

AiApaec

620
1
6
12

That did not work for me, because `.Find` does not accept Lambdas, it only accepts a list of objects. Hence you have to pass the houseId directly, i.e. `var house = db.Houses.Find(houseId);` – Matt Nov 18 '15 at 13:15

score 0 · Answer 2 · edited May 23 '17 at 11:58

The .find() method is not strongly typed, it uses object parameters and hence the error you describe occur during runtime, if the data type of the parameters passed to it are incompatible (e.g. you pass a string where an integer value is expected).

Generally, I usually prefer to use .Where() and / or .FirstOrDefault() rather than .Find(), because you have to specify the field names, e.g.

var usr=db.Employees.Where(
    x => x.FirstName=="James" && x.LastName=="Bond").FirstOrDefault();

or you could directly write:

var usr=db.Employees.FirstOrDefault(
    x => x.FirstName=="James" && x.LastName=="Bond");

Both return Null if no records were found.

It makes the code more clear for later reviews and possibly required data model changes - consider if you have to add a field to a compound primary key, such as birth date in the example above: In this case it is easy to see that you have to add it to the .Where statement.

Because it is strongly-typed, it also allows you to use intellisense to look up the proper data type of the fields involved in the query by right-clicking and selecting "go to definition."

All these benefits make trouble-shooting much easier. Also, .Where and .FirstOrDefault are more versatile than .Find, because they support multiple kinds of sequences (look here at SO for a more detailed explanation) and are not restricted to primary keys.

The downside is that .Any(), .Single(), .First(), .Where() (and their ...OrDefault() pendents) are generating SQL queries and hence roundtrips to the database, in a similar way as if you would use ad-hoc queries or call stored procedures.

However, if you're not searching within the primary key, you have to use them. The tool LinqPad shows nicely how such queries are translated by EF into SQL code (assuming Northwind as example database):

SELECT TOP (1) 
[Extent1].[EmployeeID] AS [EmployeeID], -- primary key (PK)
[Extent1].[LastName] AS [LastName], [Extent1].[FirstName] AS [FirstName], 
[Extent1].[Title] AS [Title], [Extent1].[TitleOfCourtesy] AS [TitleOfCourtesy], 
[Extent1].[BirthDate] AS [BirthDate], [Extent1].[HireDate] AS [HireDate], 
[Extent1].[Address] AS [Address], [Extent1].[City] AS [City], 
[Extent1].[Region] AS [Region], 
[Extent1].[PostalCode] AS [PostalCode], [Extent1].[Country] AS [Country], 
[Extent1].[HomePhone] AS [HomePhone], [Extent1].[Extension] AS [Extension], 
[Extent1].[Photo] AS [Photo], [Extent1].[Notes] AS [Notes], 
[Extent1].[ReportsTo] AS [ReportsTo], [Extent1].[PhotoPath] AS [PhotoPath]
FROM [dbo].[Employees] AS [Extent1]
WHERE (N'James' = [Extent1].[FirstName]) AND (N'Bond' = [Extent1].[LastName])

Here you can see that it makes sense to restrict the fields in the resultset wherever possible, otherwise the generated query will return values you're not interested in and return an unnecessarily high amount of data.

The .Find() method will only work here with the EmployeeID, since that is the primary key (PK). For all other fields involved in queries, you can't use .Find() and have to use the other query methods (.Where(), .Single(), .First() or .Any()).

In your particular case it would look like (note you should only create a new object if required, so I have moved that to the if statement):

string houseId = data[0];
House dummy = db.Houses.FirstOrDefault(x=>x.HouseId==houseId);
if (dummy==null)
{
    House house = new House()
    {
        HouseId = houseId
    };
    db.Houses.Add(house);
}

But note in this case, it can be further optimized by using .Any():

string houseId = data[0];
if (!db.Houses.Any(x => x.HouseId == houseId))
{
    House house = new House()
    {
        HouseId = houseId,
    };
    db.Houses.Add(house);
}

if you don't need the to retrieve the object from database anyway, which avoids returning unnecessary data (as mentioned before).

`.Find()` tries to retrieve the entity from the EF internal cache before hitting the database. It is different from using `.Where()`/`.FirstOrDefault()`. Also `.Any()` **is** retrieving the object from database so there isn't much optimisation there. — rexcfnghk, Nov 17 '15 at 09:16
I have different information, please [read this](http://stackoverflow.com/q/6421127/1016343) at StackOverflow about caching of query results. For more detailed information about caching, look [here at MSDN](https://msdn.microsoft.com/en-us/data/hh949853.aspx). — Matt, Nov 17 '15 at 10:03
Please read [this](https://msdn.microsoft.com/en-us/data/jj573936). Perhaps my terminology is misleading. By *retrieving the entity from EF internal cache* I mean retrieving the entity *tracked by the context*, as mentioned in the documentation — rexcfnghk, Nov 18 '15 at 01:26
I think it depends which EF version you are using and which data context. I am referring to EF version 6 and DbContext. About caching queries I'd like to quote, taken from [here](http://mehdi.me/ambient-dbcontext-in-ef6/): "Whenever you query an entity by its primary key, the DbContext will first attempt to retrieve it from its first-level cache before defaulting to querying it from the database." And "query" means any Linq query. — Matt, Nov 18 '15 at 08:02
I am also referring to EF version 6 and `DbContext`. I am afraid that article might have been wrong: A simple console program with two different `DbContext` instances, **two** consecutive calls of `context.Houses.Single(h => h.HouseId == "HouseId")`/`context.Houses.Find("HouseId")`, and a SQL profiler can easily show that **two** SQL queries are generated to the database (by `Any`/`Single`/`First`/`Where`), whereas `Find` will only result in **one** query — rexcfnghk, Nov 18 '15 at 09:53
To be clear, I ran **two** LINQ queries for each of the **two separate** `DbContext` instances: `var foo = context.Houses.Single(h => h.HouseId == "HouseId")` and `var bar = context.Houses.Single(h => h.HouseId == "HouseId")`. I compared what the SQL server profiler captured with `var foo = context.Houses.Find("HouseId")` and `var bar = context.Houses.Find("HouseId")`. — rexcfnghk, Nov 18 '15 at 10:04
I have done some research, and found the same results as you did. Hence I updated my answer, hoping it is more clear now (I also learned from it). Thank you! — Matt, Nov 18 '15 at 12:02

score 0 · Answer 3 · answered Nov 18 '15 at 20:50

I managed to fix this weird bug. In the Context-Constructor I have simply added

public ContextEv(string dbName) : base("name=" + dbName)
{
           //Database.SetInitializer(new DropCreateDatabaseAlways<ContextEv>());
}

However at first I had to delete every table manually because the code above didn't really do it. And after letting it run once I had to comment the code and run again so the table appear (why, I really do not know.. maybe some of you know).

Thank you everyone very much for your help! I really learned some things with your answers.

Entity Framework throws exception when using Find-method

3 Answers3