SQL to Linq with join

[Matthijs de Z]

Hi,

could someone help me out with a conversion from SQL to Linq code.

normally I use a MySQL database and query a table with this SQL query:

select DatesInstr1.myDate,DatesInstr1.NormPrice as
PriceInstr1,DatesInstr2.NormPrice as PriceInstr2,
(DatesInstr1.NormPrice/DatesInstr2.NormPrice) as Ratio
from (select * from rsdata.normalizeddata where instrID=1)
DatesInstr1
join (select * from rsdata.normalizeddata where instrID=2)
DatesInstr2
on DatesInstr1.myDate=DatesInstr2.myDate

uses columns are difined as followed:

`myDate` int(8) not null
`instrID` int(11) not null
`NormPrice` double(12,5)

I now have the same table available in C# as a datatable (contains
some more columns then those used here) and I would like to query the
datatable to get the same result / output (including column names
etc).

So far, I wasn't able to get it working at all.
It would be great if someone could shed his/hers light on it.
kind regards,

Matthijs

 

[Alberto Poblacion]

Hi,

could someone help me out with a conversion from SQL to Linq code.

normally I use a MySQL database and query a table with this SQL query:

select DatesInstr1.myDate,DatesInstr1.NormPrice as
PriceInstr1,DatesInstr2.NormPrice as PriceInstr2,
(DatesInstr1.NormPrice/DatesInstr2.NormPrice) as Ratio
from (select * from rsdata.normalizeddata where instrID=1)
DatesInstr1
join (select * from rsdata.normalizeddata where instrID=2)
DatesInstr2
on DatesInstr1.myDate=DatesInstr2.myDate

uses columns are difined as followed:

`myDate` int(8) not null
`instrID` int(11) not null
`NormPrice` double(12,5)

I now have the same table available in C# as a datatable (contains
some more columns then those used here) and I would like to query the
datatable to get the same result / output (including column names
etc).

So far, I wasn't able to get it working at all.
It would be great if someone could shed his/hers light on it.
kind regards,

Let me give it a try:

var DatesInstr1=from x in table.AsEnumerable()
where x.Field<long>("instrID")==1
select new {
myDate=x.Field<int>("myDate"),
NormPrice=x.Field<double>("NormPrice") };
var DatesInstr2=from x in table.AsEnumerable()
where x.Field<long>("instrID")==2
select new {
myDate=x.Field<int>("myDate"),
NormPrice=x.Field<double>("NormPrice") };
var result = from v1 in DatesInstr1
join v2 in DatesInstr2 on v1.myDate equals v2.myDate
select new {
PriceInstr1=v1.NormPrice,
PriceInstr2=v2.NormPrice,
Ratio=v1.NormPrice/v2.NormPrice };

 

[Matthijs de Z]

Hi Alberto,

your solution works fine! (again ;-))
Thank you very much.
regards,

Matthijs