views:

30

answers:

1

Hi,

I wonder if someone can help me out translating a MySQL query to (Db)LINQ statement.

I’ve made a test case for what I want to do, so data and structure are different than what I really want to do, but it’s just meant to get it working.

In a MySQL database I have this table:

CREATE TABLE `mytable1` 
( 
   `id` int(11) NOT NULL AUTO_INCREMENT, 
   `number` int(11) NOT NULL, 
   `name` varchar(20) COLLATE latin1_general_ci NOT NULL, 
   PRIMARY KEY (`id`) 
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci 

This is the content

id      number  name   
1       10      aaa   
2       20      bbb   
3       25      cccc   
4       30      ddd   
5       35      eee   

I use DBLinq within C# to connect to this table. In the C# program, I also have a list like this:

List<myDataFields> myNewData = new List<myDataFields>(); 

With

public class myDataFields   
{   
    public int number { get; set; }   
    public string name { get; set; }   
}   

Content of the list is:

number  name     
10      firstName   
20      secondName   
30      ThirdName   

If the content of the list would be a table in mysql (table2), I could just update table1 with this query:

update mytable1,mytable2 set mytable1.name=mytable2.name where 
mytable1.number=mytable2.number; 

(in real life I actually have to match two columns to be a key)

The result will be

id      number  name   
1       10      firstName   
2       20      secondName   
3       25      cccc   
4       30      ThirdName   
5       35      eee   

But how can I update table1, using DBLinq and the List within c#? Kind regards,

Matthijs