score:2

Accepted answer

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;
});

score: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/


Related Query

More Query from same tag