Translating Sql to Linq

Question

How to translate

select *from
   (
     select EmployeeID,FirstName,LastName,Region from Employees where city
     in ('London','Seattle') 
   )
   x
where x.Region is not null

into Linq Equivalent.

I tried (But null values also get selected)

LinqDBDataContext Context = new LinqDBDataContext();

   var query = from emps in

                  (
                       from empls in Context.Employees
                       where empls.City == "London" || empls.City == "Seattle"
                        && empls.Region != null
                             select new
                              {
                                 FirstName = empls.FirstName,
                                 LastName = empls.LastName,
                                 City = empls.City,
                                 Region = empls.Region
                              }
                   )
              select emps;


            GridView1.DataSource = query;
            GridView1.DataBind();

Answer 1

Threadpool, you need to take a look at a product called Linqer (I have no association with it). It is fantastic. It converts SQL queries to LINQ. I have yet to come across a query it cannot handle.

Answer 2

It's your parentheses. You're missing them around your City's OR statement.

LinqDBDataContext Context = new LinqDBDataContext();

   var query = from emps in

                  (
                       from empls in Context.Employees
                       where (empls.City == "London" || empls.City == "Seattle")
                        && empls.Region != null
                             select new
                              {
                                 FirstName = empls.FirstName,
                                 LastName = empls.LastName,
                                 City = empls.City,
                                 Region = empls.Region
                              }
                   )
              select emps;


            GridView1.DataSource = query;
            GridView1.DataBind();

Answer 3

Your sql can be reritten as:

select EmployeeID,FirstName,LastName,Region 
from Employees 
where city in ('London','Seattle') 
and Region is not null

Answer 4

You need to change your where clause to be:

where (empls.City == "London" || empls.City == "Seattle") && empls.Region != null

Without the parens you would get nulls with London