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.

Answer 1

This is the LINQ to SQL forum.  Also you should consider putting everything in english.

Regards

Answer 2

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();  "internal exception"

Answer 3

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

Answer 4

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 ");
                    }
                }
            }

Answer 5

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

Answer 6

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

Answer 7

Sorry i tried this code, get always the same exception

Answer 8

Hi DevDOTNETns,

Could you please post the inner exception information here?

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 9

An error occurred when starting a transaction on the connection provider. For details, see the inner exception.

Answer 10

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
Chris

Answer 11

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; }
        }
    }
}

Answer 12

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

Chris

Answer 13

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(); 
            }

Answer 14

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