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