2

How can I convert following SQL query to LINQ in C#?

I don't need all columns from both tables and the result set should be 

IEnumerable<DataRow>

Query:

select 
    c.level, cl.name, c.quantity
from 
    dbo.vw_categories c
left join 
    dbo.vw_categoriesLocalization cl on c.level = cl.level 
                                     and cl.language = 'en'
where 
    c.level like '000%' 
    and c.level <> '000';

Expected:

IEnumerable<DataRow> result = ????

Thanks in advance..

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Adam Right
  • 955
  • 6
  • 17
  • 35

2 Answers2

2

Here's how you would write the query:

var query =
    from c in db.vw_categories
    join cl in db.vw_categoriesLocalization on c.level equals cl.level into clo
    from cl in clo.DefaultIfEmpty()
    where (cl == null || cl.language == "en")
          && c.level.StartsWith("000")
          && c.level != "000"
    select new
    {
        c.level,
        name = cl == null ? null : cl.name,
        c.quantity
    }

To convert that to IEnumerable<DataRow> maybe you could do something like this:

var datarows = query.Select(r => {
    var row = dataTable.NewRow();
    row["level"] = r.level;
    row["name"] = r.name;
    row["quantity"] = r.quantity;
    return row;
});
Gabe
  • 84,912
  • 12
  • 139
  • 238
  • Good answer. But I have one question here: can we do it by writing one time instead of two times. First it is fetching as our need then it converts query result to DataRow. If I will follow that one then why I will not use normal logic to loop over and add records to datatable. Any suggestion.. – Manas Kumar Dec 09 '15 at 11:34
1

If using LINQ to SQL:

var ctx = new SomeDataContext();
from c in ctx.vw_categories
join cl in ctx.vw_categoriesLocation
on c.level equals cl.level into g
from clv in g.DefaultIfEmpty()
where (clv == null || clv.language == "en")
&& c.level.Contains("000%")
&& c.level != "000"
select new
{
  c.level,
  name = (clv != null) ? clv.name : default(string),
  quantity = c.quantity
};

More info here: http://codingsense.wordpress.com/2009/03/08/left-join-right-join-using-linq/

Brian Mains
  • 50,520
  • 35
  • 148
  • 257