c# - Linq Query with join on subquery -


basically i'm trying write query joins on select top 1 second table like:

select sum(pinfo.quantity + p.itemcount),     i.owner    invoice     join purchase_info pinfo       on pinfo.invoice = i.invid     join (select distinct sku,                           productlineid,                           itemcount             products productlineid in (13, 14)) p       on p.sku = pinfo.item  i.owner = 22623  group i.owner

here's pathetic attempt in linq has invalid syntax, ideas appreciated.

(from in _invoicerepository.table  join pi in _purchaseinforepository.table on i.invoiceid equals pi.invoiceid  join p in (from p2 in _productrepository.table p2.sku == pi.item select new { p2.sku, p2.itemcount }).take(1)  on pi.item equals p.sku   i.memberid == memberid &&  (p.productlineid == (int)productlines.inkcartridges ||   p.productlineid == (int)productlines.tonercartridges)  select pi.quantity * p.itemcount) .defaultifempty(0) .sum();

here first stab @ this.

from sql, looks want find how many ink , toner cartridges particular customer has ordered ever.

this should give same results sql (this depending on order of products table since taking top 1 without sort of ordering being done:

  var count = in _invoicerepository.table               i.ownerid == memberid               select new                 {                   ownerid = i.ownerid,                   totalproductcount = i.purchases.sum(pro => pro.products                                                       .where(p => p.productlineid == (int)productlines.inkcartridges ||                                                          p.productlineid == (int)productlines.tonercartridges)                                                       .take(1)                                                       .sum(p => p.itemcount * pro.quantity))                 };

since did not know the classes of 3 objects (invoice, purchaseinfo, , product) made guess @ are:

invoice class: assume has list/collection of purchaseinfos

public class invoice {   public int id { get; set; }   public int ownerid { get; set; }    public list<purchaseinfo> purchases { get; set; } }

purchaseinfos: invoice has multiple purchaseinfos, each 1 links (ideally) 1 product since sku not unique assome has list/collection of products in it.

public class purchaseinfo {   public int id { get; set; }   public int quantity { get; set; }    public int invoiceid { get; set; }   public invoice invoice { get; set; }    public int item {get;set;}   public list<product> products { get; set; } }

product class: assome there id field (not shown) or composite primary key somewhere

public class product {   public int sku { get; set; }   public int productlineid { get; set; }   public int itemcount { get; set; }    public list<purchaseinfo> purchaseinfos { get; set; } }

hopefully can take need. if way off, please update question class definitions (you can remove unneeded properities if want) better answer can produced.


Comments

Post a Comment

Popular posts from this blog

jquery - How can I dynamically add a browser tab? -

since using iframes house external pages are, accounts "not preferred method" , disallowed sites (flickr, one), want dynamically generate browser tabs. e.g., when user submits form, want dynamically generate tab on browser in response submitted. e.g., might want add new tab url like: http://www.bigsurgarrapata.com/contact how in jquery? you want form target attribute action attribute. http://www.w3schools.com/tags/att_form_target.asp browsers have ultimate control on result of target attribute open new tab value of _blank. <form action="contact_submit.php" target="_blank" method="post">
Read more

keyboard - C++ GetAsyncKeyState alternative -

i using windows.h library's getasynckeystate function define key pushed on keyboard . here program returns int value of pushed key example. #include <iostream> #include <windows.h> using namespace std; int main (){ char i; for(i=8;i<190;i++){ if(getasynckeystate(i)== -32767){ cout<<int (i)<<endl; } } return 0; } but trying make opengl simple game , function appeared slow . there function not need cycle 180 times , if statement . thanks you're trying make opengl game, you're going use glfw. if that's case can away using glfwgetkey() such: if (glfwgetkey(glfw_key_up) == glfw_press) {...} if (glfwgetkey(glfw_key_down) == glfw_press) {...} that method nicely explained in tutorial 6 opengl-tutorial.org . should start first tutorial of beginners tutorials , later intermediate tutorials . if you're going make win32 window can listen wm_keydown or wm_keyup messages , virtua...
Read more

android - java.net.UnknownHostException(Unable to resolve host “URL”: No address associated with hostname) -

i developing app testing asp.net webservice in android.for simple webservice adding 2 numbers used.where numbers passed parameters.the result in dialogue.the webserver works in local. when application runs following result exception.i provided internet permission in manifest. java.net.unknownhostexception(unable resolve host “ http://url.com/ ”: no address associated hostname) my code given below main activity public static string rslt=""; /** called when activity first created. */ @override public void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); setcontentview(r.layout.activity_main); button b1=(button)findviewbyid(r.id.button1); final alertdialog ad=new alertdialog.builder(this).create(); b1.setonclicklistener(new onclicklistener() { @override public void onclick(view arg0) { // todo auto-generated method stub try { ...
Read more