countrymeister
asked on
ROW_NUMBER , LEFT OUTER JOIN in LINQ
I have a List<int> dateList
20130531
20130601
20130603
and a DataTable dt with three columns
(Code string,
BusDate int,
Price double
)
ABC 20130531 5
ABC 20130602 4
ABC 20130603 7
LMN 20130601 3
LMN 20130602 2
I need to get for all the given dates in the List, the corresponding codes and prices.
If the date does not exist in the datatable, get the previous days price. Order by Code and date
The final result set should look like this based on the above example
ABC 20130531 5
ABC 20130601 5
ABC 20130603 7
LMN 20130531 NULL
LMN 20130601 3
LMN 20130603 2
This does not work
var query = from c in dateList.AsEnumerable()
join p in dt.AsEnumerable() on c equals p["BusDate"] into ps
from p in ps.DefaultIfEmpty()
select new { BusDate = c, Code = p["Code"], Price = p["Price"] };
20130531
20130601
20130603
and a DataTable dt with three columns
(Code string,
BusDate int,
Price double
)
ABC 20130531 5
ABC 20130602 4
ABC 20130603 7
LMN 20130601 3
LMN 20130602 2
I need to get for all the given dates in the List, the corresponding codes and prices.
If the date does not exist in the datatable, get the previous days price. Order by Code and date
The final result set should look like this based on the above example
ABC 20130531 5
ABC 20130601 5
ABC 20130603 7
LMN 20130531 NULL
LMN 20130601 3
LMN 20130603 2
This does not work
var query = from c in dateList.AsEnumerable()
join p in dt.AsEnumerable() on c equals p["BusDate"] into ps
from p in ps.DefaultIfEmpty()
select new { BusDate = c, Code = p["Code"], Price = p["Price"] };
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.