Click here to Skip to main content
15,893,594 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear Gentle man,

I want to write an LINQ Query for the normal sql query given below. Could you please help in acheiving it so that i can learn from this.

SQL
select a.ParcelCode,a.StatusDate,b.boxMachineName,c.StationName,a.ParcelStatus,b.newStatus
,b.storedPackType
,b.PhoneNo from ParcelsHistories a join tbl_PackStatus b on a.ParcelCode=b.packCode
Join Stations c
on b.boxMachineName=c.StationCode where (cast(CONVERT(varchar(8),a.StatusDate, 112)
AS datetime) >= @fromdate
and cast(CONVERT(varchar(8), a.StatusDate, 112) AS datetime) <= @todate) and
a.ParcelStatus=@ParcelStatus
order by a.StatusDate



Thanks
Posted

I prefer linq extension methods so that's how I'll present my solution:
C#
DataContext db = new DataContext

public static IQueriable<class1> QMethod( // You could use anonymous types or return a DataTable as in the example above
  DateTime fromDate,
  DateTime toDate,
  int parcelStatus,
  DataContext db = null) // I like staggered queries so this is habit
{
    if(db==null)
      db = new DataContext();

    return db.ParcelsHistories
      .Join(
        db.tbl_PackageStatus,  //First Join (i'll use your table alias ie: a,b...)
        a=>a.ParcelCode,       //ParcelsHistories join field
        b=>b.packCode,         //tbl_PackageStatus join field
        (a,b)=> new {a,b})     //new anonymous type 
      .Join(db.Stations,
        n=>n.b.boxMachineName, //I used 'n' for the anon type.  Within this method it's members are known so will appear in intelisense
        c=>c.StationCode,
        (n,c)=>new{n.a,n.b,c}) //now we have access to all fields.  Nothing has been selected yet and our where clauses will refine these joins for us.  Let Linq do the work ^_^
      .Where(  //All the where clauses (only just noticed they are all on table 'a' so I could have put these before the first join.  The SQL query that linq produces will be identical in either case
        n=>n.a.StatusDate > fromDate
        && n.a.StatusDate <= toDate
        && n.a.ParcelStatus == parcelStatus)
      .Select( //Select the data into a known type so that any other class / method can access the data without having to worry about anon type access trickery
        n=> new Class1{ //.Net3.5 trick.  Could also use a class constructor
          ParcelCode = n.a.ParcelCode,
          StatusDate = n.a.StatusDate,
          boxMachineName = n.b.boxMachineName,
          StationName = n.c.StationName,
          ParcelStatus = n.a.ParcelStatus,
          newStatus = n.b.newStatus,
          storedPackType = n.b.storedPackType,
          PhoneNo = n.b.PhoneNo}
      )

/*
* No query is performed on the database yet.  This method returns an IQueriable object 
* that contains no data.  You can further add the the query after is has been returned.
* The query is only performed when you use an extension method on an IQueriable object 
* such as .ToList(), .FirstOrDefault() or .Count() and many more, although those last
* two will change the SQL query to make it more efficient.  .Count() will ignore your 
* selection criteria for eg.
* You could have this method return a List and call .ToList() on the end of the return.
* That would have the effect of performing the query there and then and returning the
* results in a List<class1>.  Don't forget to change the return type ^_^
*/        
}
 
Share this answer
 
v3
Comments
damodara naidu betha 11-Oct-12 7:06am    
5+
var qury = from a in ParcelsHistories.AsEnumerable()
               join b in tbl_PackStatus.AsEnumerable()
               on a.Field<decimal>("ParcelCode") equals b.Field<decimal>("packCode")
               join c in Stations.AsEnumerable()
               on b.Field<decimal>("boxMachineName") equals c.Field<decimal>("StationCode")
               where Convert.ToDateTime(a.StatusDate) >= Convert.ToDateTime(@fromdate)
                         && Convert.ToDateTime(a.StatusDate) >= Convert.ToDateTime(@todate)
                         && Convert.ToInt32(a.ParcelStatus) == Convert.ToInt32(@ParcelStatus)
                         select new
                         {
                             ParcelCode = a.Field<decimal>("ParcelCode"),
                             StatusDate = a.Field<DateTime>("StatusDate"),
                             boxMachineName = b.Field<string>("boxMachineName"),
                             StationName = c.Field<string>("StationName"),
                             ParcelStatus = a.Field<decimal>("ParcelStatus"),
                             newStatus = b.Field<decimal>("newStatus"),
                             storedPackType = b.Field<decimal>("storedPackType"),
                             PhoneNo = b.Field<decimal>("PhoneNo")

                         };
              if (qury.Count>0)
              {

                  DataTable dt = new DataTable();
                  dt.Columns.Add("ParcelCode");
                  dt.Columns.Add("StatusDate");
                  dt.Columns.Add("boxMachineName");
                  dt.Columns.Add("StationName");
                  dt.Columns.Add("ParcelStatus");
                  dt.Columns.Add("newStatus");
                  dt.Columns.Add("storedPackType");
                  dt.Columns.Add("PhoneNo");

                  foreach (var item in qury)
                  {
                      DataRow dr = dt.NewRow();
                      dr["ParcelCode"] = item.ParcelCode;
                      dr["StatusDate"] = item.StatusDate;
                      dr["boxMachineName"] = item.boxMachineName;
                      dr["StationName"] = item.StationName;
                      dr["ParcelStatus"] = item.ParcelStatus;
                      dr["newStatus"] = item.newStatus;
                      dr["storedPackType"] = item.storedPackType;
                      dr["PhoneNo"] = item.PhoneNo;
                      dt.Rows.Add(dr);
                  }
              }
 
Share this answer
 
v4

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900