none
foreach tolist() RRS feed

  • 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.

    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

    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
  • 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"

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


    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
    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
    Saturday, May 19, 2012 10:31 AM
  • Sorry i tried this code, get always the same exception
    Monday, 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
    Moderator
  • 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

    view inner exception in the exception assistantHTH
    Chris
    Monday, 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; }
            }
        }
    }



    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

    Chris

    Monday, 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

    Tuesday, May 22, 2012 10:02 AM