Now Imagine that I'm reading a CSV file which has the following fields ( this is not the case, but will simplify things a lot ):
ArticleNumber, ArticleName, SupplierName
And the data would look like:
XXX1, 1_Name, Philips
XXX2, 2_Name, Samsung
XXX3, 3_Name, HP
XXX4, 4_Name, Samsung
What I want is one big loop which itterates each line, creates an article for each line and creates the supplier. All this by using Linq2Entities.
As you've noticed line 2 and 4 have Samsung as the same supplier. So the supplier will already exist when the itteration reaches line 4 and it should not create the supplier again ( SupplierName is UNIQUE in the table ), but it should update the Samsung's ArticleCount
field.
The only way I currently see how I can solve this is to itterate the file twice and to first create the suppliers and then do a first SaveChanges and on the second time I create the articles and increment the suppliers their articleCount and do another SaveChanges.
Anyone got the correct solution for this?
PS: No need to give me full functioning code. I just want to see when you use your SaveChanges and other relevant methods.
If you have imported the tables into the edmx file（I mean you are using EntityFramework instead of Linq）——You can just see that there are two navigation properties generated in both of the entity models called "Suppliers"(in Articlee) and "Article"(in Supplier)
models，so you can just use a single looping：
foreach(Supplier s in SomeArticleInstance.Suppliers)
{
//Do with s here.
}
But please be sure that your tables have been created relationships with in advance in the SQL Management Studio in the Diagram folder.
Thanks for the reply, but I'm not sure if you guys are answering what I'm asking.
I understand your getId method and the first time it sees "Samsung" it'll see that Samsung does not exist yet as a supplier and thus adds a new supplier "Samsung", but what happens when it encounters the second "Samsung"? It will not "exist" yet because
you haven't called SaveChanges so it'll try to create a second "Samsung". When you finally call SaveChanges it'll see 2 "Samsung" suppliers and then throw an exception. This is what I need a solution for ... do you call some kind of SaveChanges every time
you add a new supplier or something?
I hope I'm clear now and that you guys understand the question :)
it'll see that Samsung does not exist yet as a supplier and thus adds a new supplier "Samsung", but what happens when it encounters the second "Samsung"? It will not "exist" yet because you haven't called SaveChanges so it'll try to create a second "Samsung".
Then your Samsung must have a primary key that is identified from 1 stepped by 1，otherwise you have to do this by re-writting the constructor of your Samsung in its constructor：
public class Samsung
{
private static int Id=0;
…………
public Samsung()
{
Id++;
YourPrimaryKey = Id;
}
}
Thus everytime when you inserting one item, it will be automatically increasing.
Yannick86
Member
565 Points
366 Posts
Inserting records together with foreign key records
Oct 12, 2012 01:47 PM|LINK
Dear,
I'm in the following ( simplified & mutated, so don't ask me why something makes no sense :p ) situation:
I have 2 tables ARTICLE and SUPPLIER like the following:
ARTICLE
- ID ( PK )
- ArticleNumber
- ArticleName
- SupplierID ( Foreign Key to the SUPPLIER table )
SUPPLIER
- ID ( PK )
- SupplierName ( UNIQUE )
- ArticleCount
Now Imagine that I'm reading a CSV file which has the following fields ( this is not the case, but will simplify things a lot ):
ArticleNumber, ArticleName, SupplierName
And the data would look like:
XXX1, 1_Name, Philips
XXX2, 2_Name, Samsung
XXX3, 3_Name, HP
XXX4, 4_Name, Samsung
What I want is one big loop which itterates each line, creates an article for each line and creates the supplier. All this by using Linq2Entities.
As you've noticed line 2 and 4 have Samsung as the same supplier. So the supplier will already exist when the itteration reaches line 4 and it should not create the supplier again ( SupplierName is UNIQUE in the table ), but it should update the Samsung's ArticleCount field.
The only way I currently see how I can solve this is to itterate the file twice and to first create the suppliers and then do a first SaveChanges and on the second time I create the articles and increment the suppliers their articleCount and do another SaveChanges.
Anyone got the correct solution for this?
PS: No need to give me full functioning code. I just want to see when you use your SaveChanges and other relevant methods.
Thanks in advance,
- Yannick
thaicarrot
Contributor
5120 Points
1459 Posts
Re: Inserting records together with foreign key records
Oct 12, 2012 03:41 PM|LINK
It is Many-To-One
foreach(var item in cvs)
{
Article a = new Article();
a.ArticleNumber = item.ArticleNumber;
a.ArticleName = item.ArticleName;
a.SupplierName = GetID(item.SupplierName);//This line should store FK not Full Name.
this.Context.Article.Add(a);
}
this.Context.SaveChanges();
private int GetID(string supplier)
{
if(supplier == null)return;
var item = this.Context.SUPPLIER.Where(o => o.SupplierName == supplier).FirstOrDefaults();
if(item != null)
return item.ID;
else
this.Context.SUPPLIER.Add(new SUPPLIER {SupplierName = supplier});
return null;
}
Hand writting.
Weera
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Inserting records together with foreign key records
Oct 14, 2012 01:05 AM|LINK
Hi，
If you have imported the tables into the edmx file（I mean you are using EntityFramework instead of Linq）——You can just see that there are two navigation properties generated in both of the entity models called "Suppliers"(in Articlee) and "Article"(in Supplier) models，so you can just use a single looping：
foreach(Supplier s in SomeArticleInstance.Suppliers) { //Do with s here. }But please be sure that your tables have been created relationships with in advance in the SQL Management Studio in the Diagram folder.
Yannick86
Member
565 Points
366 Posts
Re: Inserting records together with foreign key records
Nov 05, 2012 08:06 AM|LINK
Thanks for the reply, but I'm not sure if you guys are answering what I'm asking.
I understand your getId method and the first time it sees "Samsung" it'll see that Samsung does not exist yet as a supplier and thus adds a new supplier "Samsung", but what happens when it encounters the second "Samsung"? It will not "exist" yet because you haven't called SaveChanges so it'll try to create a second "Samsung". When you finally call SaveChanges it'll see 2 "Samsung" suppliers and then throw an exception. This is what I need a solution for ... do you call some kind of SaveChanges every time you add a new supplier or something?
I hope I'm clear now and that you guys understand the question :)
Thanks in advance!
- Yannick
Yannick86
Member
565 Points
366 Posts
Re: Inserting records together with foreign key records
Nov 20, 2012 01:29 PM|LINK
Bump :(
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Inserting records together with foreign key records
Nov 21, 2012 12:12 AM|LINK
Then your Samsung must have a primary key that is identified from 1 stepped by 1，otherwise you have to do this by re-writting the constructor of your Samsung in its constructor：
public class Samsung { private static int Id=0; ………… public Samsung() { Id++; YourPrimaryKey = Id; } }Thus everytime when you inserting one item, it will be automatically increasing.