Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Can someone help convert this SQL to LINQ?
Message
General information
Forum:
ASP.NET
Category:
LINQ
Environment versions
Environment:
VB 9.0
OS:
Windows Server 2012
Network:
Windows 2008 Server
Database:
MS SQL Server
Application:
Web
Miscellaneous
Thread ID:
01605212
Message ID:
01605216
Views:
47
The simple answer seems to be to break it into two separate LINQ queries. I only need to verify via SQL Profiler that it hits the backend one time.
//select sum(pos.balance * coalesce(units.formula,1)) as on_order,poi.uniq_key

var NotInStatuses = new string[] {"CANCEL","CLOSED"};
NotInStatuses.Dump();

var query1 = from poi in mvw_PoItems
join pom in mvw_PoMain
	on poi.ponum equals pom.ponum
join si in mvw_SupplierInfo
	on pom.uniqsupno equals si.uniqsupno
join pos in mvw_PoItemsSchedule
	on poi.uniqlnno equals pos.uniqlnno
join inv in mvw_Inventory
	on poi.uniq_key equals inv.uniq_key
join units in mvw_Units
	on inv.pur_lunit equals units.to into unitsjoin
	from myUnits in unitsjoin.DefaultIfEmpty()
where !NotInStatuses.Contains(pom.postatus)
	&& pos.balance != 0
	&& poi.poittype != "MRO"
	&& poi.lcancel != true
orderby poi.uniq_key	
	select new 
	{
		poi.uniq_key,
		poi.ponum,
		pos.schd_date,
		balance = (decimal?)pos.balance * (decimal?)((decimal?)myUnits.formula ?? (decimal?)1),
		si.supname
	};


var q2 = (from q1 in query1 group q1 by q1.uniq_key into grouped
		select new {
			grouped.Key,
			balance = grouped.Sum (g => g.balance)
		}).OrderBy (q => q.Key);

q2.Dump();
query1.Dump();
Previous
Reply
Map
View

Click here to load this message in the networking platform