Thursday, August 11, 2011

Any Mehtod

Shout it kick it on DotNetKicks.com
Here I am oging to discuss about Any method. One purpose of this method is to check either the collection has elemetn or not.
Example
List<string> members = 
         new List<string>() { "pranay", "Hemang" };
   bool ISCollectionEmpty = members.Any();             
So by using method I get to know my collection has any element or not.
So when I run above coe I get true in my boolean variable if there is not element it return flase.

Now cosider the below Database Table and  LINQ to SQL dbml file

Department table

Employee table


As you can see there is one to many relationship between Department and Employee.

Problem Statement
Now Here I want to list out only those department whihc has employee.

Solution
Most of the people do the gorup by and make use of the join and then try to find out the department which has solution.

But the better solution to this is make use of Any() method available in the System.Linq for the collection as below
var deptList = from dept in dbcontext.Departments
                           where dept.Employees.Any()
                           select dept;

   foreach (var item in deptList)
   {
      Console.WriteLine(item.Name + " : " + item.Employees.Count());
   }
Output:

As you can see the above query fetch the those department only which has employee and remove the department those doesnt have any.
I can easily able to get the cout of the employees in department using count method.

Sql query :
When you see the Sql profiler or get the query in visual studio by watching variable.
SELECT [t0].[Id], [t0].[Name]
FROM [dbo].[Department] AS [t0]
WHERE EXISTS(
  SELECT NULL AS [EMPTY]
  FROM [dbo].[Employee] AS [t1]
  WHERE [t1].[DeptId] = [t0].[Id]
)

No comments:

Post a Comment