cross table / object update using (DB)linq

[Matthijs de Z]

Hi,

I wonder if someone can help me out translating a mysql query so a
(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

 

[Matthijs de Z]

Solved the problem like this:

List<myDataFields> myDFList = new List<myDataFields>();
myDFList.Add(new myDataFields { name= "name123", number=
20 });
myDFList.Add(new myDataFields { name = "name456", number =
35 });

int[] getNumbers = (from numbers in myDFList select
numbers.number).ToArray();

var dataInDb = from x in _db.mytable1 where
getNumbers.Contains(x.Number) select x;

foreach (var row in dataInDb)
{
row.NAAm = (from mdf in myDFList where mdf.number ==
row.Number select mdf.name).First();
}

_db.SubmitChanges();


If someone knows a better solutions, please let me know.
regards,

Matthijs