locked
LINQ to SQL question about updating RRS feed

  • Question

  • Hi, I have a database with two tables (Parents and Children) -- Foreign Key = ChildID, Primary Key = ParentID. Each Parent gets charged a different amount based on the rate of the child. My idea was to use a LINQ query to get all children with the same rate, and then use a foreach loop to charge all of their respective parents. The code below is not working and I was wondering if I'm missing something...or if there is a better, entirely different way to do this. 

    var myKids = from children in dB.Childrens
                     where children.Rate.Cycle == 3
                     select children;
    
                foreach (var i in myKids)
                {
                    Parent parent1 = dB.Parents.Single(p => p.ParentID == i.ParentID);
                    parent1.Balance += 100;
                }
                dB.SubmitChanges();


    Please help.

    Thanks.
    Saturday, August 1, 2009 3:39 PM

Answers

  • ok. Now let's fill a DataSet and create some relations.

    //Quick and dirty filltable so I can demonstrate the relations
    public DataTable FillTable(String tableName, Int32 primaryKeyColumn)
    {
         String sql = String.Format("SELECT * FROM {0}", tableName);
         DataTable table = new DataTable();
         using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))
         {
              da.Fill(table);
         }
         table.TableName = tableName;
         table.PrimaryKey = new DataColumn[] { table.Columns[primaryKeyColumn] };
         return table;
    }

    //Fill tables
    DataSet ds = new DataSet();
    DataTable parent = FillTable("Parent", 0);
    DataTable child = FillTable("Children", 0);
    DataTable rates = FillTable("Rates", 0);

    //Add to DataSet
    ds.Tables.Add(parent);
    ds.Tables.Add(children);
    ds.Tables.Add(rates);

    //Add relations
    ds.Relations.Add("Parent_Children",
                    ds.Tables["Parent"].Columns["ParentID"],
                    ds.Tables["Children"].Columns["ParentID"],
                    true); 
    ds.Relations.Add("Rates_Children",
                    ds.Tables["Rates"].Columns["RateID"],
                    ds.Tables["Children"].Columns["RateID"],
                    true); 

    //Example
    foreach (DataRow parentRow in ds.Tables["Parent"].Rows)
    {
        Console.WriteLine("{0} {1}", parentRow["FirstName"], parentRow["LastName"]);
        Console.WriteLine("---------------------------------");
        foreach (DataRow childRow in parentRow.GetChildRows("Parent_Children"))
        {
             DataRow ratesRow = childRow.GetParentRow("Rates_Children");
             Console.WriteLine("Child: {0} (1}", childRow["FirstName"], childRow["LastName"]);
             Console.WriteLine("RateName: {0}", ratesRow["RateName"]);
        }
    }

    I did this in notepad, so there may be typos. This gives you an idea of what yu can do.

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Edited by JohnGrove Sunday, August 2, 2009 4:37 PM
    • Proposed as answer by Harry Zhu Friday, August 7, 2009 1:47 AM
    • Marked as answer by Harry Zhu Monday, August 10, 2009 6:35 AM
    Saturday, August 1, 2009 4:56 PM

All replies

  • List the tables like so:

    Parent {ParentID, parentName}
    Child {ChildID, etc............}

    So we can understand your schema

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Saturday, August 1, 2009 4:11 PM
  • Parent {ParentID, LastName, FirstName, Balance}
    Children {ChildID, ParentID, RateID, LastName, FirstName}
    Rates {RateID, RateName, Cycle}

    Relationships: (Child has a Parent) (Child has a Rate)



    I'm thinking it would be easier (and it would make more sense) if the Parents could "know" about their children. Something like this:

    var parentsInCycle3 = from parents in dB.Parents
    		      where parents.Childrens.Rate.Cycle == 3      <--- I know this doesn't work, I just need something that could function like that.
    		      select parents;
    
    foreach (var i in parentsInCycle3)
       {
    	i.Balance += 100;
       }

    Saturday, August 1, 2009 4:34 PM
  • ok. Now let's fill a DataSet and create some relations.

    //Quick and dirty filltable so I can demonstrate the relations
    public DataTable FillTable(String tableName, Int32 primaryKeyColumn)
    {
         String sql = String.Format("SELECT * FROM {0}", tableName);
         DataTable table = new DataTable();
         using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))
         {
              da.Fill(table);
         }
         table.TableName = tableName;
         table.PrimaryKey = new DataColumn[] { table.Columns[primaryKeyColumn] };
         return table;
    }

    //Fill tables
    DataSet ds = new DataSet();
    DataTable parent = FillTable("Parent", 0);
    DataTable child = FillTable("Children", 0);
    DataTable rates = FillTable("Rates", 0);

    //Add to DataSet
    ds.Tables.Add(parent);
    ds.Tables.Add(children);
    ds.Tables.Add(rates);

    //Add relations
    ds.Relations.Add("Parent_Children",
                    ds.Tables["Parent"].Columns["ParentID"],
                    ds.Tables["Children"].Columns["ParentID"],
                    true); 
    ds.Relations.Add("Rates_Children",
                    ds.Tables["Rates"].Columns["RateID"],
                    ds.Tables["Children"].Columns["RateID"],
                    true); 

    //Example
    foreach (DataRow parentRow in ds.Tables["Parent"].Rows)
    {
        Console.WriteLine("{0} {1}", parentRow["FirstName"], parentRow["LastName"]);
        Console.WriteLine("---------------------------------");
        foreach (DataRow childRow in parentRow.GetChildRows("Parent_Children"))
        {
             DataRow ratesRow = childRow.GetParentRow("Rates_Children");
             Console.WriteLine("Child: {0} (1}", childRow["FirstName"], childRow["LastName"]);
             Console.WriteLine("RateName: {0}", ratesRow["RateName"]);
        }
    }

    I did this in notepad, so there may be typos. This gives you an idea of what yu can do.

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Edited by JohnGrove Sunday, August 2, 2009 4:37 PM
    • Proposed as answer by Harry Zhu Friday, August 7, 2009 1:47 AM
    • Marked as answer by Harry Zhu Monday, August 10, 2009 6:35 AM
    Saturday, August 1, 2009 4:56 PM
  • Thanks John. That would be a good way to do it. 

    My application is built around LINQ to SQL and I was wondering if there is a way to do this using only LINQ queries and not using datasets and datatables.

    Any ideas would be appreciated.
    Saturday, August 1, 2009 5:30 PM
  • You initially stated, "or if there is a better, entirely different way to do this". So in my humble opinion, that is what I demonstrated.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Saturday, August 1, 2009 5:38 PM
  • By the way, you can use LINQ to query the DataTables.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Saturday, August 1, 2009 5:44 PM