where子句中需要的LINQ查询帮助 [英] LINQ Query help needed in where clause

问题描述

亲爱的绅士，

我想为下面给出的普通sql查询编写一个LINQ查询.您能否帮助实现它，以便我可以从中学习.

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.

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

推荐答案

我更喜欢linq扩展方法，因此这就是我提出解决方案的方式:

I prefer linq extension methods so that''s how I''ll present my solution:

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 ^_^
*/        
}

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);
                  }
              }

这篇关于where子句中需要的LINQ查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！