How can I convert the following SQL queries into LINQ query form in C#, .NET 3.5 code: 1)
select COUNT(distinct Skill_Name)
from Table1
where Department = 'ABC' and Skill_Name is not null
2)
select distinct location, country from Customer where Customer_Code ='1001';
I suspect you want:
var query = from entry in dbContext.Table1
where entry.Department == "ABC" && entry.SkillName != null
select entry.SkillName;
var count = query.Distinct().Count();
Or using extension method syntax, in one go:
var count = dbContext.Table1
.Where(entry => entry.Department == "ABC" &&
entry.SkillName != null)
.Select(entry => entry.SkillName)
.Distinct()
.Count();
As shown by mesiesta, you can combine query expressions with calls not supported within query expressions, but I tend to assign the query expression to an intermediate variable... I personally find it clearer, but use whichever you (and your team) prefer.
Count() call at the end. You can count the number of results when you've fetched them down to the datatable.Count() call there. I don't know about transferring into an existing DataTable, but you can use the LINQ to DataSet extension methods to create a new DataTable from a query result. See DataTableExtensions.CopyToDataTableSomething like this
int count = (from p in Table1
where p.Department == "ABC" && p.Skill_Name != null
select p.Skill_Name).Distinct().Count();
For second query you can use this
var query= (from p in Customer
where p.Customer_Code=="1001"
select new { Location=p.location ,Country=p.country}).Distinct();
you can use linqpad to convert to linq and lambda expressions
