将SQL查询转换为linq quert [英] convert SQL query to linq quert

问题描述

select distinct t1.field1,t2.field2 from table1 t1 join table2 t2 on t1.field1=t2.field1 and t1.field2=t2.field2 and t1.field3=t2.field3 where t1.field4=1 and t1.datefield between t2.startdate and t2.enddate

推荐答案

如果你想要它要成为Linq-to-SQL，你可能需要稍微改变我的样本（如果只是为了引用数据上下文并且可能修复了我引入的大写差异......）。



如果您更喜欢'sql-like C＃语法''请原谅我 - 我更喜欢方法链语法（我想我也花了很久以来用C / C ++ / C＃来喜欢那些新奇的查询语言关键词:)。



尽管有免责声明，这里有一个C＃Linq表达式（以及一些支持代码所以它至少可以编译）我认为会完成你的工作：

If you want it to be Linq-to-SQL, you may have to change up my sample below a bit (if only to reference the data context and maybe fix capitalisation differences I''ve introduced...).

If you prefer the ''sql-like C# syntax'' please forgive me - I prefer the method-chain syntax (I guess I''ve spent too long in C/C++/C# to like those new-fangled query language keywords :).

Disclaimers notwithstanding, here is a C# Linq expression (and some supporting code so it at least compiles) that I think will do your job:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
  /*
   *  select distinct t1.field1,t2.field2 
   *  from table1 t1 
   *  join table2 t2 on t1.field1=t2.field1 and t1.field2=t2.field2 and t1.field3=t2.field3 
   *  where t1.field4=1 and t1.datefield between t2.startdate and t2.enddate
   * */
  class Class1
  {
    public void XX()
    {
      List<Table1> table1 = new List<Table1>();
      List<Table2> table2 = new List<Table2>();

      var x =
        table1
          .Where(t1 => t1.field4 == 1)
          .Join(table2,
              t1 => new { t1.field1, t1.field2, t1.field3 },
              t2 => new { t2.field1, t2.field2, t2.field3 },
              (t1, t2) => new { t1, t2 })
          .Where(j => j.t1.dateField > j.t2.startDate && j.t1.dateField < j.t2.endDate)
          .Select(j2 => new {j2.t1.field1, j2.t2.field2 })
          .Distinct();
    }
  }

  public class Table1
  {
    public string field1 { get; set; }
    public string field2 { get; set; }
    public string field3 { get; set; }
    public int field4 { get; set; }
    public DateTime dateField { get; set; }
  }

  public class Table2
  {
    public string field1 { get; set; }
    public string field2 { get; set; }
    public string field3 { get; set; }
    public DateTime startDate { get; set; }
    public DateTime endDate { get; set; }
  }
}

这篇关于将SQL查询转换为linq quert的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！