none
Get Eqvivelant Linq Query RRS feed

  • Question

  • select bi.buyer_part_no,bi.AlternateID,bi.[name],sum(bi.packets) from bill a join (select b.srl,b.qty,b.grossqty,b.rate,b.amount,b.dond_rt,b.dond_Amt, b.td_rt,b.td_amt,b.tds_rt,b.tds_amt, b.packingAmt, b.ttl_tags,b.bed_rt,b.bed_Amt,b.UL,b.bed_on_amt,b.ecess_rt,b.ecess_amt,b.hecess_rt, b.hecess_amt,b.saletax_rt,b.lstamt,b.nccd_rt,b.nccdamt,b.aedamt, b.aed_rt,b.packing_rt, b.finyear, e.doc_gl, e.doc_no, e.po_no,e.po_dt,d.srl oaitem_srl, case when d.buyer_part_no is null then j.alias else d.buyer_part_no end as buyer_part_no, j.alias itemalias,j.name, Convert(varchar,j.itemgroupid) +'-'+ Convert(varchar,j.id) itemno,b.uom,b.remark, b.purchaseitems_ui,b.grnitem_ui,Convert(varchar,g.doc_no)+'-'+ Convert(varchar,f.srl) pvkey, b.ui,b.bill_ui,b.item_ui,b.oaitem_ui, b.assessableValue,b.assessableAmtpp,b.prevat,b.log_date, a.nt_username,a.host_name,convert( varchar,e.doc_gl)+'-'+ convert(varchar,e.doc_no) +'-'+ convert(varchar,d.srl) as oakey, j.packet_qty,b.packets,j.alternateid from billitem b left join bill a on b.bill_ui=a.ui left join item j on b.item_ui=j.ui left join oaitem d on b.oaitem_ui=d.ui left join oa e on d.oa_ui=e.ui left join purchaseitems f on f.ui = b.purchaseitems_ui left join purchase g on g.ui = f.purchase_ui join maingl i on i.gl_code = a.doc_gl where a.doc_dt between '20190801' and '20190831' and i.AcntBookNosID in (4,19,26,33,34,35,36,37,44,55,63,64) ) bi on bi.bill_ui=a.ui where a.doc_gl=4 and a.doc_no between 21091 and 21091 and a.finyear=2019 group by bi.buyer_part_no,bi.AlternateID,bi.[name]

    Can some one help to get equivelant Linq Query
    Tuesday, August 6, 2019 5:30 PM

All replies

  • Not without the objects that represent that SQL query. For the most part it will be similar just substituting your .NET object names in place of tables and .NET property names in place of columns. However all this assumes you've already configured your data layer to properly read the data using an ORM or ADO.NET.

    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, August 6, 2019 5:50 PM
    Moderator
  • .net code

                CursorAdapter cursor0 = new CursorAdapter();
                cursor0.SelectCmd = @"select cast( a.doc_gl as varchar)+'-'+ cast( a.doc_no as varchar) billkey,
    					a.doc_dt,
    					b.name+'-'+isnull(b.lst_no,'') as acname,
    					a.Amt,a.Remark,
    					a.Amount,a.dondAmt,a.td_amt,a.tds_amt,a.packingAmt,a.bed_amt,a.aed_amt,
    					a.ecess_amt,a.hecess_amt,a.nccd_amt,a.lstamt,a.balanceamt,a.misc_charg,a.vehicle_no,
    					cast( b.gl_code as varchar)+'-'+ cast( b.sl_code as varchar)as ac_code,
    					cast( c.gl_code as varchar)+'-'+cast( c.sl_code  as varchar)as del_code,
    					c.name as deliveryname,
    					d.reportname, d.reportpapername,d.roundamt,
    					b.roundamt,
    					a.due_dt,a.removal_dt,a.finyear,a.PlantID,
    					a.doc_gl,a.connect2,a.doc_no,a.lc_ui,
    					a.mainsl_ui,a.ui,delivery_ui,a.db_cr,a.assessableValue,a.PrintCopy,a.prevat,
                        a.fromdate,a.todate,a.oldrate,a.allpo,a.log_date,
                        a.nt_username,a.host_name,a.billadvice_id,a.DebitNoteAdvice_id,
                        a.RdOnBill_ui,c.lst_no,isnull(c.pob,1) pob
    				from Bill a
    					join mainsl b on b.ui=a.mainsl_ui
    					left join mainsl c on c.ui=a.delivery_ui
    					join maingl d on d.gl_code=a.doc_gl 
    				where a.doc_dt between @fromdate and @uptodate 
    				 and d.AcntBookNosID in (4,19,26,33,34,35,36,37,44,55,63,64)  
    				order by a.connect2,a.doc_no";
                cursor0.SendUpdate = true;
                cursor0.SqlTable = "Bill";
                cursor0.updatableFieldList = @"ui,doc_gl,connect2,doc_dt,finyear,doc_no,removal_dt,vehicle_no,due_dt,Amount,
    					dondAmt,td_amt,tds_amt,packingAmt,bed_amt,aed_amt,
    					ecess_amt,hecess_amt,nccd_amt,lstamt,Amt,Remark,balanceamt,misc_charg,db_cr,mainsl_ui,lc_ui,delivery_ui,
    					PlantID,assessableValue,PrintCopy,prevat,fromdate,
                        todate,oldrate,allpo,log_date,nt_username,
                        host_name,billadvice_id,DebitNoteAdvice_id,RdOnBill_ui";
                cursor0.keyfields = "ui";
                cursor0.AutoGuidColumn = "ui";
                //cursor0.AutoIncColumns= "id";
                cursor0.beforecursorfill +=
                    new CursorAdapter.beforecursorfillEventHandler(cursor0_beforecursorfill);
                cursor0.BuildAdapter(ref ds, ref o0, SqlDataBase.FillType.Data);
    
    CursorAdapter cursor6 = new CursorAdapter();
                //convert(bit,1) as recordok,b.xise_head,
                cursor6.SelectCmd = @"select b.srl,b.qty,b.grossqty,b.rate,b.amount,b.dond_rt,b.dond_Amt,
    					b.td_rt,b.td_amt,b.tds_rt,b.tds_amt, b.packingAmt,
    					b.ttl_tags,b.bed_rt,b.bed_Amt,b.UL,b.bed_on_amt,b.ecess_rt,b.ecess_amt,b.hecess_rt,
    					b.hecess_amt,b.saletax_rt,b.lstamt,b.nccd_rt,b.nccdamt,b.aedamt, b.aed_rt,b.packing_rt,
    					b.finyear, e.doc_gl, e.doc_no, e.po_no,e.po_dt,d.srl oaitem_srl,
    					case when d.buyer_part_no is null 
                            then j.alias
                            else d.buyer_part_no end as buyer_part_no,
    					j.alias itemalias,j.name, Convert(varchar,j.itemgroupid) +'-'+ Convert(varchar,j.id) itemno,b.uom,b.remark,
    					b.purchaseitems_ui,b.grnitem_ui,Convert(varchar,g.doc_no)+'-'+ Convert(varchar,f.srl) pvkey,
    					b.ui,b.bill_ui,b.item_ui,b.oaitem_ui, b.assessableValue,b.assessableAmtpp,b.prevat,b.log_date,
    					a.nt_username,a.host_name,convert( varchar,e.doc_gl)+'-'+ convert(varchar,e.doc_no) +'-'+ convert(varchar,d.srl) as oakey,
                        j.packet_qty,b.packets,j.alternateid
    				from billitem b 
    					left join bill a on b.bill_ui=a.ui                 
    					left join item j on b.item_ui=j.ui
    					left join oaitem d on b.oaitem_ui=d.ui
    					left join oa e on d.oa_ui=e.ui
    					left join purchaseitems f on f.ui = b.purchaseitems_ui
    					left join purchase g on g.ui = f.purchase_ui
    					join maingl i on i.gl_code = a.doc_gl
    					where a.doc_dt between @fromdate and @uptodate 
    					and i.AcntBookNosID in (4,19,26,33,34,35,36,37,44,55,63,64)
                        order by a.connect2,a.doc_no,b.srl";
                cursor6.SendUpdate = true;
                cursor6.updatableFieldList = @"ui,bill_ui,item_ui,oaitem_ui,srl,qty,grossqty,rate,amount,dond_rt,dond_Amt,
    				td_rt,td_amt,tds_rt,tds_amt,packingAmt,ttl_tags,remark,bed_Amt,nccdamt,bed_on_amt,bed_rt,ecess_rt,ecess_amt,hecess_rt,hecess_amt,
    				UL,saletax_rt,lstamt,aedamt,nccd_rt,aed_rt,packing_rt,finyear,uom,purchaseitems_ui,grnitem_ui,
    				assessableValue,assessableAmtpp,prevat,log_date,nt_username,host_name,packets";
                cursor6.SqlTable = "billitem";
                cursor6.keyfields = "ui";
                //cursor6.AutoIncColumns = "id";
                cursor6.beforecursorfill += new CursorAdapter.beforecursorfillEventHandler(cursor0_beforecursorfill);
                cursor6.BuildAdapter(ref ds, ref o6, SqlDataBase.FillType.Data);
    DataRelation r1 = new DataRelation("r1", ds.Tables["Bill"].Columns["ui"], ds.Tables["billitem"].Columns["bill_ui"]);
                ForeignKeyConstraint fk1 = new ForeignKeyConstraint(r1.ParentColumns, r1.ChildColumns);
                fk1.DeleteRule = Rule.Cascade;
                fk1.UpdateRule = Rule.Cascade;
                fk1.AcceptRejectRule = AcceptRejectRule.Cascade;

    i tried to write linq query like this

    var query = from p in ds.Tables["bill"].AsEnumerable()
                                             join i in ds.Tables["billitem"].AsEnumerable()
                                             on p.Field<Guid>("ui") equals i.Field<Guid>("bill_ui")
                                             where p.Field<int>("doc_gl") == docgl
                                             && p.Field<int>("doc_no") >= frombill
                                             && p.Field<int>("doc_no") <= tobill
                                             group i by i.Field<string>("buyer_part_no") into grp
                                             select new
                                             {
                                                 cpn = grp.Key,
                                                 packets=grp.Sum(a=>a.Field<decimal>("packets"))
                                             };
    
    
                                foreach (var q in query)
                                {}

    can some one help to add 

    alternateid,name columns from billitem tables 
    an sql query equvivelant 
    • Edited by Sushil Agarwal Tuesday, August 6, 2019 11:13 PM missing info
    Tuesday, August 6, 2019 11:08 PM
  • i got the working Linq query as under

    var query = from p in bill.AsEnumerable()
                                             join i in billitem.AsEnumerable()
                                             on p.Field<Guid>("ui") equals i.Field<Guid>("bill_ui")
                                             where p.Field<int>("doc_gl") == docgl
                                             && p.Field<int>("doc_no") >= frombill
                                             && p.Field<int>("doc_no") <= tobill
                                             group i by new
                                             {
                                                 cpn=i.Field<string>("buyer_part_no"),
                                                 mfplid=i.Field<string>("alternateid"),
                                                 cpname=i.Field<string>("name"),
                                             } into grp
                                             select new
                                             {
                                                 cpn = grp.Key.cpn,
                                                 cpname = grp.Key.cpname,
                                                 mfplid=grp.Key.mfplid,
                                                 packets =grp.Sum(a=>a.Field<int>("packets"))
                                             };

    can experts advice good larning sites for new bee to linq,how to write SQL in  Linq style

    Tuesday, August 6, 2019 11:39 PM
  • The MSDN documentation for LINQ describes pretty much every scenario in terms of SQL statements so I'd start there.

    As for your code you were using a cursor in the first example but seem to be using a DataSet/DataTable in the second one so it is unclear what your actual data is being stored in. Nevertheless if the above LINQ query is working then you should be fine. It could probably be optimized a little bit but the above is clear enough that performance shouldn't be an issue unless you're dealing with 1000s of rows in which case using DataSet isn't a good idea anyway.


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, August 7, 2019 12:20 AM
    Moderator