2

I am using Migrations in an MVC 4 EF5 application in Visual Studio 2012 Express with SQL Server 2012 Express, using Code First.

I use the Seed method in configuration.cs, firstly creating a Tags table. When I execute 'Update-Database -verbose -force' from Package Manager, it works correctly and doesn't create duplicate tags - and re-creates them if deleted:

db.Tags.AddOrUpdate(
   t => t.Name,
   new Tag { Name = "Bakery", NamePlural = "Bakeries" },
   new Tag { Name = "Bar", NamePlural = "Bars" },
   new Tag { Name = "Bookshop", NamePlural = "Bookshops" }
);

db.SaveChanges();

I then try and add related Places data:

db.Places.AddOrUpdate(
p => p.Name,
new Place
{
  Name = "Shoreditch Grind",
  URL = "shoreditch-grind-cafe",
  Address = "213 Old St",
  City = "London",
  PostCode = "EC1V 9NR",
  Website = "www.shoreditchgrind.com",
  Phone = "020 7490 0101",
  About = "Good coffee on the Silicon Roundabout",
  Image = "noimage.png",
  Tag = db.Tags.Single(t => t.Name == "Bar")
},

new Place
{
  Name = "The Old Blue Last",
  URL = "old-blue-last-pub",
  Address = "38 Great Eastern St",
  City = "London",
  PostCode = "EC2A 3ES",
  Website = "www.theoldbluelast.com",
  Phone = "020 7739 7033",
  About = "Pub of Vice Magazine",
  Image = "noimage.png",
  Tag = db.Tags.Single(t => t.Name == "Bakery")
}
);

This however creates duplicates, adding all the places again every time I execute 'Update-Database -verbose -force'

I'm new to MVC - and I also don't fully understand what this does:

p => p.Name,

I have a feeling perhaps I should be manually adding ID values to each object?

How can I run this without creating duplicate Places?

It would also be useful to be able to mark each Tag.Name as unique simply.

Thanks.

niico
  • 11,206
  • 23
  • 78
  • 161
  • Every time you run `update-database` the `Seed()` method is called, which I think is why you're getting duplicates. If you actually look at your tables - I suspect there may be duplicate rows of Places, but these duplicates will (obviously) have different primary keys? – MattSull May 17 '13 at 12:16
  • Yeah they have different Primary Keys (PlaceID). It doesn't create duplicates in the Tags table however. What's best way to fix it? Thx – niico May 17 '13 at 12:18

1 Answers1

3

This may work:

var place = new Place
{
  Name = "The Old Blue Last",
  URL = "old-blue-last-pub",
  Address = "38 Great Eastern St",
  City = "London",
  PostCode = "EC2A 3ES",
  Website = "www.theoldbluelast.com",
  Phone = "123 456 789",  // updated number
  About = "Pub of Vice Magazine",
  Image = "noimage.png",
  TagID = db.Tags.Single(t => t.Name == "Bakery").TagID
};
db.Places.AddOrUpdate(p => p.Name, place);
db.SaveChanges();

Since "The Old Blue Last" is already there, and we've updated based on p.Name, it should only update that entry changing Phone to "123 456 789". This similar to what you have tried, but may work. See more here.

You also mentioned that you are not sure what p => p.Name does. The => is called a Lambda Expression. It is an anonymous function. It is a method without a declaration, access modifier, return type, name etc. It's a short hand expression that allows you to write a method in the place you are going to use it.

See more here and here..

Community
  • 1
  • 1
MattSull
  • 5,514
  • 5
  • 46
  • 68
  • Thanks for the explanation on p => p.Name. I'm 90% there now - what does it do for you in the AddOrUpdate expression above - why do you need to call an anonymous function? (testing the other code now) – niico May 18 '13 at 09:34
  • `AddOrUpdate` takes a key/value pair. The key, in your case, is `p => p.Name`. EF takes this key (which will be the name of the place you're trying to insert) and checks it against the DB to see if there are any records that match it. The value, in your case, is the new 'Place' you're trying to insert. If there's no match in the DB, a new record is inserted. If there is, the record will be updated. Using the lambda in this situation allows you to, by short-hand, access a property of `Place`. – MattSull May 18 '13 at 10:49
  • Thanks. So why can't you just type in the property 'Place' - instead of the Lambda? – niico May 18 '13 at 11:20
  • The code example above runs fine on the first 'Update-Database -verbose -force' on an empty table. On the second run it fails: "An error occurred while updating the entries. See the inner exception for details." – niico May 18 '13 at 11:22
  • Error: "Running Seed method. System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Places_dbo.Tags_TagID". The conflict occurred in database "myAppMVC4", table "dbo.Tags", column 'TagID'." – niico May 18 '13 at 11:23
  • You use the lambda because you can just go `Place`, because what is place in that context? Passing in `p` is like passing in a place object, so you can go `p.Name`. Is there definitely `Tag` record in the database with the name "Bakery"? That error means you're either passing in a null value or that the value doesn't exist. – MattSull May 18 '13 at 11:45
  • Yes there is a record there - it works the first time I run it - and relates the correct tag record (with ID 243). Then I run it the second time and it comes up with that error – niico May 18 '13 at 11:46
  • I was just about to ask did you have a navigation property in your `Place` model, something like`TagId`. The error you got explains what you needed to do i.e. the FK constraint isn't satisfied. – MattSull May 18 '13 at 11:51
  • I've just made a test view - with this in the controller: Tag tag = db.Tags.Single(t => t.Name == "Bakery"); return View(tag); It returns the bakery record with no problems. – niico May 18 '13 at 11:54
  • Yes I have TagID in the place model... [DisplayName("Tag")] [Required(ErrorMessage= "Tag is Required")] public virtual int TagID { get; set; } – niico May 18 '13 at 11:55
  • each place can be associated with 1 tag. Could it be that it's trying to associate it with a tag twice? The place model also has this: public virtual Tag Tag { get; set; } - is that wrong? – niico May 18 '13 at 11:56
  • The FK error? No, AFAIK, you can't go `Tag = ...`, you have to use `TagId` because that's the FK. So is your problem solved? Don't forget to accept as correct answer if so, and if you thought it helped, you can up-vote too. – MattSull May 18 '13 at 11:58
  • sorry you've lost me. It's not fixed (only works if I use TagID = 243, rather than Tag = db.Tags.Single(t => t.Name == "Bakery") - which isn't what I want. You had Tag = db.Tags.Single(t => t.Name == "Bakery") in your example - is that wrong? – niico May 18 '13 at 12:03
  • Not sure if it'll work, but look at my edit. If it doesn't, try removing the `Tag` all together. If that doesn't work, I'm stumped. – MattSull May 18 '13 at 12:28
  • I've removed any reference to Tag in the Places object - and it works fine. – niico May 18 '13 at 12:30
  • You basically need a way to set the FK without hard coding it like in the way that worked in you comment above. I'm not near a machine with VS so can't be of any more help. Make a new question, something like "Entity Framework - how to set FK (1:1) using AddOrUpdate in Seed method without hard coding it". Good luck. – MattSull May 18 '13 at 12:42
  • 1
    OK I solved it - super simple - thanks for helping me get there... TagID = db.Tags.Single(t => t.Name == "Bakery").TagID – niico May 18 '13 at 13:21
  • It's always the simple way - good work. I'll update my answer with your solution to make it complete. – MattSull May 18 '13 at 14:17