Entity Framework returns null object when row has null values

Question

I have an existing database (which I cannot modify in any way) that I'd like to use EF to code against. There are no primary keys, foreign keys or actual relationships between tables - everything seems to be handled by applications using the DB. For most of the tables/entities, this has been trivial. However, I've got one class, like the one below (simplified) that I'm struggeling with.

[Table("tableName")]
public class MyClass
{
    [Key]
    [Column("id")]
    public int? Id { get; set; }

    [Column("name")]
    public string CompanyName { get; set; }

    [Column("address")]
    public string Address { get; set; }
}

Things like _context.MyClasses.Where(x => x.Address == "something") work fine. However, it seems that when the id column is NULL in the DB, i get a null object back - even though CompanyName and Address have values. And I need those values, regardless of whether id (or whatever else) is null. Is there some way to force EF to generate the objects and ignore empty columns?

Removing or movin the [Key] attribute gives me an exception like "Must have a key column" (and the Id-column is also used to map a collection of other classes into this one if there are any that match).

UPDATE Don't think about the Id-column as an actual identifier. It's basically just an integer with a stupid name.

how can a PK be null ... not by technical option, but by usage ... — user57508
– user57508, Commented Jul 6, 2012 at 9:06
It's not really a PK in the DB, it just seems to have to be marked as Key in the entity for it to be able to collect some other entities into it... — Arve Systad
– Arve Systad, Commented Jul 6, 2012 at 9:07
btw for "Must have a key column" exists a workaround: just mark all columns as key (like EF does it with ambigous views), or add a new column (auto inc) — user57508
– user57508, Commented Jul 6, 2012 at 9:09
You are out of luck if I'd can be null. You need a non null key for EF. If name and address are unique and non null you could make that your PK for EF... — Denis Troller
– Denis Troller, Commented Jul 6, 2012 at 9:12
such a database is not a good candidate for an OR/M as EF and neither NH, maybe you can have some satisfation by using dapper dot net instead — Felice Pollano
– Felice Pollano, Commented Jul 6, 2012 at 9:12

Michal B. · Accepted Answer · 2012-07-06 09:08:29Z

3

You cannot put a KeyAttribute above Id property if it is nullable in your database. That's what's causing you the problems...

The primary keys cannot be nullable, but I guess you know that...

answered Jul 6, 2012 at 9:08

Michal B.

5,7187 gold badges45 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arve Systad Over a year ago

That seems reasonable, but why does EF throw an exception if I remove it? As mentioned, there's no actual PK in the database.

UserControl Over a year ago

@ArveSystad, No, it isn't reasonable. Consider a log table where there is no unique key at all (you may have it but that's often not needed). Does that mean you can't query such a table with EF?

ChrisF · Accepted Answer · 2012-07-06 09:08:46Z

2

You must have a Key column and that column must contain values for this to work. Marking a column as Key indicates that this is the Primary Key column for that table.

If the Key column contains a null value then that row won't be returned to the client. If the Id column isn't the primary key (as it can contain a null value) then you need to add a new column that can act as the primary key.

answered Jul 6, 2012 at 9:08

ChrisF

138k31 gold badges265 silver badges335 bronze badges

5 Comments

Arve Systad Over a year ago

But then - why can't i remove the Key-attribute without getting an exception back? I've got other similar tables which has no key (neither in DB nor model).

ChrisF Over a year ago

@ArveSystad - because you need a Key column.

Arve Systad Over a year ago

I've got another class/table which works perfectly fine without one, so I cannot really see why...

Arve Systad Over a year ago

..nevermind what I said. The other entity ofcourse uses the EntityNameId convention to get its key.

Arve Systad Over a year ago

Seems like the solution will be either to make the responsible ones change the DB, or to write SQL manually. :-(

Halo · Accepted Answer · 2012-07-06 09:14:33Z

1

You need to have a Key column. EF requires you to have one. For your purposes, though, you can use an alternative way:

Set your Id's StoreGeneratedPattern property as Identity, and leave its generation and management to EF. Do not use Id for your nullable purposes; create another column like MyId, which is Nullable and not EntityKey, and use it any way you want.

answered Jul 6, 2012 at 9:14

Halo

1,5323 gold badges24 silver badges40 bronze badges

2 Comments

Arve Systad Over a year ago

How/Where do i set the StoreGeneratedPattern property? It seems to be a Model designer-thing, and I do not have an edmx-file.

Halo Over a year ago

I believe you can put StoreGeneratedPattern="Identity" attribute on top of the model definition, though I'm not sure

Arve Systad · Accepted Answer · 2012-07-06 10:59:37Z

1

The solution

Like Andreas Niedermair commented on my question, I've now added [Key] to multiple columns in the database. I found that four columns together as a composite key is always unique in this case.

Since I'm never inserting new content to the database, this seems like a decent solution in this edge case scenario, I guess.

answered Jul 6, 2012 at 10:59

Arve Systad

5,5291 gold badge35 silver badges59 bronze badges

Collectives™ on Stack Overflow

Entity Framework returns null object when row has null values

4 Answers 4

2 Comments

5 Comments

2 Comments

The solution

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

5 Comments

2 Comments

The solution

Comments

Your Answer

Sign up or log in

Post as a guest

Related