I am trying to join 3 entity tables a fill-up data with few columns only from those tables into a data table
DataTable dttable = new DataTable();
dttable.Columns.Add("billno", typeof(String));
dttable.Columns.Add("date", typeof(DateTime));
dttable.Columns.Add("time", typeof(String));
dttable.Columns.Add("sname", typeof(String));
dttable.Columns.Add("name", typeof(String));
dttable.Columns.Add("qty", typeof(Decimal));
dttable.Columns.Add("rate", typeof(Decimal));
var rows = from mobjbmast in Context.bmasts.AsEnumerable()
join mobjbtran in Context.btrans
on mobjbmast.billno equals mobjbtran.billno
join mobjwaiter in Context.waiters
on mobjbmast.scode equals mobjwaiter.code
where mobjbmast.billno == mbillno
select dttable.LoadDataRow(new object[]
{
mobjbmast.billno,
mobjbmast.date,
mobjbmast.time,
mobjwaiter.name,
mobjbtran.name,
mobjbtran.qty,
mobjbtran.rate
}, false);
Expected the above code to fill the data table. But no rows yielded. When debugged the above code, 'rows' variable shows in the debugger
unable to evaluate the expression. Operation not supported. Unknown error: 0x80070057'
So I tried this:
var rows = from mobjbmast in Context.bmasts.AsEnumerable()
join mobjbtran in Context.btrans.AsEnumerable()
on mobjbmast.billno equals mobjbtran.billno
join mobjwaiter in Context.waiters.AsEnumerable()
on mobjbmast.scode equals mobjwaiter.code
where mobjbmast.billno == mbillno
select new { billno = mobjbmast.billno, date = mobjbmast.date, time = mobjbmast.time, sname = mobjwaiter.name, name = mobjbtran.name, qty = mobjbtran.qty, rate = mobjbtran.rate };
No change in scenario.
Tried the above with ToList()
var rows = from mobjbmast in Context.bmasts.ToList()
join mobjbtran in Context.btrans.ToList()
on mobjbmast.billno equals mobjbtran.billno
join mobjwaiter in Context.waiters.ToList()
on mobjbmast.scode equals mobjwaiter.code
where mobjbmast.billno == mbillno
select new { billno = mobjbmast.billno, date = mobjbmast.date, time = mobjbmast.time, sname = mobjwaiter.name, name = mobjbtran.name, qty = mobjbtran.qty, rate = mobjbtran.rate };
This time, the error went off but in the debugger, I see 'The Enumeration yielded no results' message.
Also tried adding DefaultIfEmpty()
.
I'm aware that I can load this output into a List<T>
. But here a data table is needed and a class for this is unnecessary.
How to structure the query to return a proper IEnumerable to convert into a data table?
Finally I solved it like this, based on suggestions from some websites
DataTable dttable = new DataTable();
dttable.Columns.Add("billno", typeof(String));
dttable.Columns.Add("date", typeof(DateTime));
dttable.Columns.Add("time", typeof(String));
dttable.Columns.Add("sname", typeof(String));
dttable.Columns.Add("name", typeof(String));
dttable.Columns.Add("qty", typeof(Decimal));
dttable.Columns.Add("rate", typeof(Decimal));
var rows = from mobjbmast in Context.bmasts.AsEnumerable()
join mobjbtran in odlsContext.btrans
on mobjbmast.billno equals mobjbtran.billno
join mobjwaiter in Context.waiters
on mobjbmast.scode equals mobjwaiter.code
where mobjbmast.billno == mbillno
let billarray = new object[]
{
mobjbmast.billno,
mobjbmast.date,
mobjbmast.time,
mobjwaiter.name,
mobjbtran.name,
mobjbtran.qty,
mobjbtran.rate
}
select billarray;
foreach (var array in rows)
{
dttable.Rows.Add(array);
}
User contributions licensed under CC BY-SA 3.0