Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Can someone help convert this SQL to LINQ?
Message
Information générale
Forum:
ASP.NET
Catégorie:
LINQ
Versions des environnements
Environment:
VB 9.0
OS:
Windows Server 2012
Network:
Windows 2008 Server
Database:
MS SQL Server
Application:
Web
Divers
Thread ID:
01605212
Message ID:
01605216
Vues:
46
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();
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform