Arnab Arnab - 9 months ago 65
C# Question

left non equi join linq

I have a list of objects in table tblB which is retrieved from db in following format.

public class playdata
public string consumerid { get; set; }
public string play_time { get; set; }
public string genre{ get; set; }
public int mycounter{ get; set; }

I have a table tblA which has a column colA which has int from 0 to 1000.

I wish to create a linq query similar to sql as below..

Select x.i as numindex, y.consumerid,y.play_time,y.genre
(Select colA as i from tblA) x
left join
Select consumerid, play_time,genre,mycounter from tblB
)y on y.mycounter > x.i

I tried the following unsuccessfully..

I came to find that Enumerable.Range(0, 1001) generated a numeric series so no need to get that table data..

List<playdata> plays = .....

var q= (from s in Enumerable.Range(0, 1001)
join p in plays on s < p.mycounter into t
from rt in t.DefaultIfEmpty() select new{
play_time =p.play_time,

I see two errors in second line..

p is not in scope of left side of equals .. I have also tried..p.mycounter>s with same result.

The other error is in into where it shows.. expected contextual keyword equals

The errors go away if I change the second line to..

join p in plays on s equals p.mycounter into t

All help is sincerely appreciated.


Answer Source

You have to use the alternative way of representing left outer join in LINQ (shown in Joins - Inner, Left, Right, Cross and Full Outer Joins):

var q =
    (from i in Enumerable.Range(0, 1001)
     from p in plays.Where(x => x.mycounter > i).DefaultIfEmpty()
     select new
         numindex = i,
         consumerid = p?.consumerid,
         play_time = p?.play_time,
         genre = p?.genre

Note that in LINQ to Objects you have to account for right side of the left outer join returning null when there is no matching element, otherwise you'll get NullReferenceException.