Answered by:
foreach tolist()

Question
-
Hi,
i'm new on Linq to entities, i tried this code to save adress in my daatabase from my datagrid and test if it exist
public class CrudSaveAdress
{
public void saveadress(string adress)
{
EDMContainer bd = new EDMContainer();
ObjectQuery<Email_Account> emilaccouts = bd.Email_Account;
var query =
from emilaccout in emilaccouts
where emilaccout.black_list == false
orderby emilaccout.ID_Email_Account
select new EmailAccountInfo
{
Email_Adress = emilaccout.email_adress
};
foreach (var ad in query)
{
if (ad.Email_Adress != adress)
{
Email_Account e = new Email_Account();
e.email_adress = adress;
e.name = "pending";
e.surname = "pending";
e.date_of_birth = "pending";
e.sex = "pending";
e.black_list = false;
bd.Email_Account.AddObject(e);
bd.SaveChanges();
MessageBox.Show("Address added succesufully");
}
else
{
MessageBox.Show("Address Is Already Exists ");
}
}
}
public class EmailAccountInfo
{
public string Email_Adress { get; set; }
}
}
}
the problem ishow to skim the list result of query
i get a exception near
bd.SaveChanges(); An error occurred when starting a transaction on the connection provider. For details, see the inner exception.
- Moved by Allen_MSDN Saturday, May 19, 2012 7:02 AM (From:LINQ to SQL)
- Edited by Enn_DynamicsAX Saturday, May 19, 2012 10:01 AM more information
Thursday, May 17, 2012 10:51 PM
Answers
-
Does that mean that the exception is no longer thrown?
Would be very nice to report progress like this, if a dozend of
anwsers try to help you out with this very issue.
THANK YOU!
The fact that the codes replicates addresses is due to your logic.
First you read all email address from "bd.Email_Account":
ObjectQuery<Email_Account> emilaccouts = bd.Email_Account; var query = from emilaccout in emilaccouts where ... select ... emilaccout.email_adress;
Then you create a new "Email_Account" object for all of these
items (unless it equals the param 'address')
foreach (var ad in query) { if (ad.Email_Adress != adress) { Email_Account e = new Email_Account(); e.email_adress = adress; .... bd.Email_Account.AddObject(e); } } bd.SaveChanges();
Of course this duplicates the existing addresses if that you've read
the same data from the same table 10 lines before.
Isn't that what it's supposed to do?
Its a lot of C#-code that does almost the same as
SELECT address from EMAIL E INSERT INTO EMAIL(address) VALUES(E.address);
in SQL, lol
- Edited by Chris-von-der-Wiese Tuesday, May 22, 2012 10:06 AM
- Marked as answer by Allen_MSDN Wednesday, May 23, 2012 6:49 AM
Tuesday, May 22, 2012 10:02 AM
All replies
-
This is the LINQ to SQL forum. Also you should consider putting everything in english.
Regards
Friday, May 18, 2012 2:15 PM -
i'm new on Linq to entities, i tried this code to save adress in my daatabase from my datagrid and test if it exist
public class CrudSaveAdress
{
public void saveadress(string adress)
{
EDMContainer bd = new EDMContainer();
ObjectQuery<Email_Account> emilaccouts = bd.Email_Account;
var query =
from emilaccout in emilaccouts
where emilaccout.black_list == false
orderby emilaccout.ID_Email_Account
select new EmailAccountInfo
{
Email_Adress = emilaccout.email_adress
};
foreach (var ad in query)
{
if (ad.Email_Adress != adress)
{
Email_Account e = new Email_Account();
e.email_adress = adress;
e.name = "pending";
e.surname = "pending";
e.date_of_birth = "pending";
e.sex = "pending";
e.black_list = false;
bd.Email_Account.AddObject(e);
bd.SaveChanges();
MessageBox.Show("Address added succesufully");
}
else
{
MessageBox.Show("Address Is Already Exists ");
}
}
}
public class EmailAccountInfo
{
public string Email_Adress { get; set; }
}
}
}
the problem ishow to skim the list result of query
i get a exception near
bd.SaveChanges(); "internal exception"
- Edited by Enn_DynamicsAX Friday, May 18, 2012 3:47 PM
- Merged by Allen_MSDN Monday, May 21, 2012 2:18 AM
Friday, May 18, 2012 3:46 PM -
Hi DevDOTNETns,
Welcome to MSDN Forum.
I'm afraind I don't understand the language you posted at the end, could you please edit it to English? This is, so I can help you more effectively.
Best Regards
Allen Li [MSFT]
MSDN Community Support | Feedback to us
Saturday, May 19, 2012 7:13 AM -
i get an exception "An error occurred when starting a transaction on the connection provider. For details, see the inner exception."
the first value is working, i get this exception in the second value.
for example; for th first adress wich already exist i get =>adress already exist
for the next adress wich is new i get =>"An error occurred when starting a transaction on the connection provider. For details, see the inner exception." in stead of "added succesufully"
foreach (var ad in query) { if (ad.Email_Adress != adress) { Email_Account e = new Email_Account(); e.email_adress = adress; e.name = "pending"; e.surname = "pending"; e.date_of_birth = "pending"; e.sex = "pending"; e.black_list = false; bd.Email_Account.AddObject(e); =>>>bd.SaveChanges(); MessageBox.Show("Address added succesufully"); } else { MessageBox.Show("Address Is Already Exists "); } } }
- Edited by Enn_DynamicsAX Saturday, May 19, 2012 10:11 AM
Saturday, May 19, 2012 10:00 AM -
So the reason why the transaction fails is revealed by the inner expections(s).
When the exception is thrown, you should click 'view details' / 'Afficher les details' in the VS exception assistant and recursively expand
the innerexception tree.
The most inner one is usually the original source of failure.
Chris- Edited by Chris-von-der-Wiese Saturday, May 19, 2012 10:25 AM
Saturday, May 19, 2012 10:18 AM -
You always create 'Email_Account' items with identical data.
Probably you get a unqiue constraint or primary key violation because
the field 'email_adress' has to be unique.
Instead of
if (ad.Email_Adress != adress) { Email_Account e = new Email_Account(); e.email_adress = adress;
it shoud be
if (ad.Email_Adress != adress) { Email_Account e = new Email_Account(); e.email_adress = ad.Email_Adress;
Checking the inner exceptions will do no harm anyhow!
Chris- Edited by Chris-von-der-Wiese Saturday, May 19, 2012 10:32 AM
- Proposed as answer by Chris-von-der-Wiese Saturday, May 19, 2012 9:23 PM
- Unproposed as answer by Chris-von-der-Wiese Monday, May 21, 2012 11:12 PM
Saturday, May 19, 2012 10:31 AM -
Sorry i tried this code, get always the same exceptionMonday, May 21, 2012 9:14 AM
-
Hi DevDOTNETns,
Could you please post the inner exception information here?
Best Regards
Allen Li [MSFT]
MSDN Community Support | Feedback to us
Monday, May 21, 2012 9:30 AM -
An error occurred when starting a transaction on the connection provider. For details, see the inner exception.
Monday, May 21, 2012 10:10 AM -
This is still the "outer" exception.
To find out what really is going on and causes the problem
you should "view the details", and have a look at inner exception
(which again may nest one or more more inner exceptions):
Looks like that in VS, in french the labels are different of course
HTH
ChrisMonday, May 21, 2012 4:13 PM -
Hi Christoph Basedau
I already tried this but i didn't understand what is the problem,
("New transaction is not allowed because there are other threads running in the session."})
thi is the code
public class CrudSaveAdress { public void saveadress(string adress) { EDMContainer bd = new EDMContainer(); ObjectQuery<Email_Account> emilaccouts = bd.Email_Account; var query = from emilaccout in emilaccouts where emilaccout.black_list == false orderby emilaccout.ID_Email_Account select new EmailAccountInfo { Email_Adress = emilaccout.email_adress }; if (emilaccouts.Count() > 0) { foreach (var ad in query) { if (ad.Email_Adress != adress) { Email_Account e = new Email_Account(); e.email_adress = adress; e.name = "pending"; e.surname = "pending"; e.date_of_birth = "pending"; e.sex = "pending"; e.black_list = false; bd.Email_Account.AddObject(e); bd.SaveChanges(); MessageBox.Show("Address added succesufully"); } else { MessageBox.Show("Address Is Already Exists "); } } } } public class EmailAccountInfo { public string Email_Adress { get; set; } } } }
- Edited by Enn_DynamicsAX Monday, May 21, 2012 7:57 PM
Monday, May 21, 2012 6:39 PM -
According to google this is because of the foreach-loop runs on an IQueryable.
When you iterate the querable the query is executed within a newly opened
transaction.
This TA will be closed just at the end of the foreach loop.
Calling SaveChanges inside foreach tries to open yet another transaction.
which causes the threading/TA exception.
Solution: Close the READ transaction before calling SaveChanges
by returning all items (using ToList() or ToArray())
foreach (var ad in query.ToList())
Of course this make sense for smaller amounts of
data only
ChrisMonday, May 21, 2012 11:09 PM -
Hi, thank you, but this code add adress in databse also if it already exist!!
if (emilaccouts.Count() > 0) { foreach (var ad in query.ToList()) { if (ad.Email_Adress != adress) { Email_Account e = new Email_Account(); e.email_adress = adress; e.name = "pending"; e.surname = "pending"; e.date_of_birth = "pending"; e.sex = "pending"; e.black_list = false; bd.Email_Account.AddObject(e); MessageBox.Show("ok"); } } bd.SaveChanges(); }
Tuesday, May 22, 2012 9:38 AM -
Does that mean that the exception is no longer thrown?
Would be very nice to report progress like this, if a dozend of
anwsers try to help you out with this very issue.
THANK YOU!
The fact that the codes replicates addresses is due to your logic.
First you read all email address from "bd.Email_Account":
ObjectQuery<Email_Account> emilaccouts = bd.Email_Account; var query = from emilaccout in emilaccouts where ... select ... emilaccout.email_adress;
Then you create a new "Email_Account" object for all of these
items (unless it equals the param 'address')
foreach (var ad in query) { if (ad.Email_Adress != adress) { Email_Account e = new Email_Account(); e.email_adress = adress; .... bd.Email_Account.AddObject(e); } } bd.SaveChanges();
Of course this duplicates the existing addresses if that you've read
the same data from the same table 10 lines before.
Isn't that what it's supposed to do?
Its a lot of C#-code that does almost the same as
SELECT address from EMAIL E INSERT INTO EMAIL(address) VALUES(E.address);
in SQL, lol
- Edited by Chris-von-der-Wiese Tuesday, May 22, 2012 10:06 AM
- Marked as answer by Allen_MSDN Wednesday, May 23, 2012 6:49 AM
Tuesday, May 22, 2012 10:02 AM