Can't auto-generate IDENTITY with AddRange in Entity Framework

Question

I don't know if it's an Entity Framework's desing choice or a wrong approach on my behalf, but whenever I try to AddRange entities to a DbSet I can't seem to get the auto-generated IDENTITY fields.

[Table("entities")]
public class Entity 
{
    [Key]
    [Column("id")]
    public long Id { get; set; }

    [Column("field")]
    public string Field { get; set; }
}

var entities = new Entity[] 
{
    new Entity() { Field = "A" },
    new Entity() { Field = "B" },
};

_dbContext.Entities.AddRange(entities);
await _dbContext.SaveChangesAsync();

//ids are still default(long) at this point!!

EDIT: Here's the updated code to show what was causing the problem: enumerables. No need to add other attributes to the entity classes.

public class Request
{
    public string Field { get; set; }

    public Entity ToEntity()
    {
        return new Entity() { Field = Field };
    }
}

public async Task<IEnumerable<long>> SaveRequests(IEnumerable<Request> requests)
{
    var entities = requests.Select(r => r.ToEntity()); //not working
    var entities = requests.Select(r => r.ToEntity()).ToArray(); //working

    _dbContext.Entities.AddRange(entities);
    await _dbContext.SaveChangesAsync();

    return entities.Select(e => e.Id);
}

I hae the same issue, var aData = referencias.Select(i => new InternalClass() { /* Id autogen, */ ... }); db.InternalClass.AddRange(aData); db.SaveChanges(); But cannot retrieve the new ids on aData.FirstOrDefault() (id = 0) Had you found more info on why it happens? — Jorge Rojas, Feb 17 '18 at 04:34

lucacelenza · Accepted Answer · 2020-07-04T09:00:40.737

36

What was causing the problem? Enumerables! Take a look at the EDIT section in my question for the solution.

EDIT: posting the updated code here as answer. The problem was in the way I used enumerables. Bottom line is you should never trust lazy loading when you need consistent results right away.

public class Request
{
    public string Field { get; set; }

    public Entity ToEntity()
    {
        return new Entity() { Field = Field };
    }
}

public async Task<IEnumerable<long>> SaveRequests(IEnumerable<Request> requests)
{
    var entities = requests.Select(r => r.ToEntity()); //not working
    var entities = requests.Select(r => r.ToEntity()).ToArray(); //working

    _dbContext.Entities.AddRange(entities);
    await _dbContext.SaveChangesAsync();

    return entities.Select(e => e.Id);
}

edited Jul 04 '20 at 09:00

answered Feb 18 '18 at 09:52

lucacelenza

1,259
1
15
28

2

This would have never occurred to me... you saved me a bunch of time! Any clue why this behaves this way? – Wes P Sep 12 '18 at 20:19
Same here! This helped me out but I'd like to understand why this is happening if anyone knows. – BenjiFB Sep 19 '18 at 01:04
Actually I don't know exactly why this happens BUT in Linq2Entities, in order to get a result, you need to "execute" each query against a DbSet with a ToArray() or - say - a Sum() method, right? I guess the same principle applies here. – lucacelenza Sep 19 '18 at 07:57
This worked for me too. Weirdly, adding `.ToArray()` to `entities` inside `AddRange` does *not* work – runeks Dec 18 '18 at 11:28
4

I think this works because `Enumerables` are meant to be immutable. They behave the same way each time they are enumerated - lazily evaluating each entry. You can think of them as a spec for generating a collection rather than being a collection themselves. When you `Select` over the entities enumerable in the return line, it enumerates again and you get the results of `.ToEntity()` on each request, not the same references as the entities that were saved. Calling `ToArray` or `ToList` returns a collection of object references, so any further changes to the objects can be seen in the collection – Patrick Stephansen Apr 15 '19 at 16:00
Thanks - although you should consider adding the code here in your answer, and not as an edit to your question. :-) – Jeppe Jul 19 '19 at 20:39

score 2 · Answer 2 · edited Jan 23 '18 at 21:27

2

Please try this, it works for Int type column, need to try on long types.

[Table("entities")]
public class Entity 
{
    [Key]
    [Column("id")]
    // this you need to tell to Ef to use Identity .
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Id { get; set; }

    [Column("field")]
    public string Field { get; set; }
}

edited Jan 23 '18 at 21:27

Matheus Lacerda

5,983
11
29
45

answered Feb 27 '17 at 08:38

Yashveer Singh

1,914
2
15
23

score 0 · Answer 3 · edited May 23 '17 at 12:25

I'm using Database First in EF 6, and after trying for a period of time, I find a possible solution.

First, Check your Table in the Database, Ensure that you defined the 'ID' column as an auto-increment primary key field, which can be declared by using something like

ID int IDENTITY(1,1) PRIMARY KEY,

when creating your table. Some related information can see here1 or here2.

or you can check the data Properties in MSSQL IDE like:

Second, Set the 'ID' column's StoreGeneratedPattern as Identity, you can do it by open the edmx file in Visual Studio, right click on the Data Column in table and select Properties, and StoreGeneratedPattern setting is in the Properties Window :

Some related article see here.

After complete things above, using EF AddRange, ID will auto increment and all works great.

public class Entity 
{
    public long Id { get; set; }
    public string Field { get; set; }
}

var entities = new Entity[] 
{
    new Entity() { Field = "A" },
    new Entity() { Field = "B" },
};

_dbContext.Entities.AddRange(entities);

score 0 · Answer 4 · answered Jul 04 '20 at 06:07

In Entity Framework Core using Code First, I got this to work by doing two things:

I added the [DatabaseGenerated(DatabaseGeneratedOption.Identity)] decorator to the Entity's Primary Key, like Yashveer Singh's answer stated
I set the new entity object's primary key to 0

After performing those two steps, I didn't need to convert IEnums to Arrays. I was simply able to run:

_dbContext.Entities.AddRange(entities);
await _dbContext.SaveChangesAsync();

Can't auto-generate IDENTITY with AddRange in Entity Framework

4 Answers4

Linked

Related