Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
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
Posted 17-Jan-13 0:04am
Edited 17-Jan-13 17:26pm
v2
Comments
d@nish at 17-Jan-13 7:06am
   
Have you tried?
CHill60 at 17-Jan-13 7:08am
   
What is your actual problem? Use the Improve question widget to post what you have tried so far, and what errors are being reported (if any)

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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 Smile | :) .
 
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; }
  }
}
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 375
1 Prakriti Goyal 242
2 Yogesh Kumar Tyagi 224
3 Sergey Alexandrovich Kryukov 158
4 CPallini 150
0 OriginalGriff 6,742
1 Sergey Alexandrovich Kryukov 5,479
2 Maciej Los 3,474
3 Peter Leow 3,313
4 DamithSL 2,505


Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 17 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100