dpv
dpv

Reputation: 157

WHERE clauses in JOIN using LINQ with lambdas

I have this situation:

I have a form in ASP.NET and I need to extract data from a mssql db. The LINQ query is build from the values that are inserted in the form.

if (ddlRegion.SelectedIndex > 0)
{
  query = query.Where(re => re.Region == ddlRegion.SelectedValue);
}
if (tbName.Text.Trim().Length > 0)
{
  query = query.Where(na => na.Name.Contains(tbName.Text));
}
var result = query.Select(res => new
                {
                    res.ColumnA,
                    res.ColumnB,
                    res.ColumnC
                });

The problem is that I need to make a join with TableB

query = query.Join(TableB, tA => tA.Code, tB => tB.CodFiscal, (tA, tB) => tA);

The original SQL command is like this:

select tA.ColumnA, tA.ColumnB, tA.ColumnC from TableA tA join TableB tB on tA.Code=tB.Code where tB.ExpireDate>=getdate() and tB.datavalabil >=getdate() 

The problem is where clauses from table tB join.

Upvotes: 1

Views: 260

Answers (1)

p.s.w.g
p.s.w.g

Reputation: 149020

You can do something like this:

query = query.Join(TableB, tA => tA.Code, tB => tB.CodFiscal, (tA, tB) => new { tA, tB })
             .Where(x => x.tB.ExpireDate >= DateTime.Now and x.tB.datavalabil >= DateTime.Now)
             .Select(x => x.tA);

Or in query syntax:

query = 
    from tA in query
    join tB in TableB on tA.Code equals tB.CodFiscal
    where tB.ExpireDate >= DateTime.Now and tB.datavalabil >= DateTime.Now
    select tA;

Upvotes: 2

Related Questions