Group by in LINQ C#

In LINQ, Group By method is used to group of source element by using single or more than one property of element.

Here is an example of Group by:

In below LINQ Query, the order records is grouped by customer ID and returns total count of order for each customer.

    var result = _dbContext.Orders.GroupBy(x => x.CustomerID)

                .Select(y => new

                {

                    CustomerID = y.Key,

                    Count = y.Count()

                }).ToList();

Group by on multiple Columns:

In below LINQ Query, the order records is grouped by State and City (more than one columns) and it returns the count of order for each City.

     var result = _dbContext.Orders.GroupBy(x => new { x.State, x.City } )

                .Select(y => new

                {

                    State= y.Key.State,

                    City = y.Key.City,

                    Count = y.Count()

                }).ToList();

Deferred Execution vs Immediate Execution of LINQ Query

In this blog, we will discuss about how deferred query execution and Immediate Query Execution works in LINQ, and what the difference between two are.

Deferred Execution: 

Deferred execution of LINQ query means it constructs the query/expression tree and it defers query execution until its value is requested. When value is required, it evaluate the execution tree in locally and then it send generated SQL to server. Deferred execution approach improves query execution performance by avoiding unnecessary database call.

Here is an example of Deferred LINQ query:

IQueryable<Order> orders = _dbContext.Orders.Where(x => x.OrderNumber == orderNumber);

foreach (Order item in orders)

      {

       new OrderData

       {

         OrderID = item.OrderID,

         OrderNumber = item.OrderNumber,

         OrderStatusCode = item.OrderStatu.StatusCode

       };

}

For above LINQ query, the SQL is not generated until the foreach statement executes.

Expression tree – expression tree is a data structure, which holds LINQ to SQL query, which will be sent to SQL server /database.

Immediate Execution: 

Immediate execution of LINQ means it enforces the LINQ query to execute and get the result immediately and there are many methods like ToList(),ToArray(), ToDictionary() executes the LINQ query immediately.

Here is an example of Immediate LINQ query: 

IList<Order> orders = _dbContext.Orders.Where(x => x.OrderNumber == orderNumber).ToList();

foreach (Order item in orders)

      {

       new OrderData

       {

         OrderID = item.OrderID,

         OrderNumber = item.OrderNumber,

         OrderStatusCode = item.OrderStatu.StatusCode

       };

}

C# LINQ : Nullable Type in where clause in LINQ Query

In this blog, I will discuss about how LINQ to SQL handle the nullable type in the where clause and in LINQ to Entites, there is no any equivalent key for SQL ‘IS NULL’ key.

Example: C# Function  PersonExistenceCheck -  checks person existence

public bool PersonExistenceCheck(PersonData personData)

        {

            bool personExist = false;

            IEnumerable<Person> persons = _db.Persons.Where(x => x.Name == personData.Name && x.PersonLoc == personData.PersonLoc);

            personExist = persons.Any();

            return personExist

        }

  

As we see above, there is where clause on on PersonLoc field, which is nullable column. 

If there are any person records, which have NULL for PersonLoc, then function will not return expected result and then Question?  Why is it not working correctly?

Explanation: In SQL, both below statement will be treated differently and output will be different.

PersonLoc == null
PersonLos Is NULL.

If PersonLoc is null

                PersonLoc == null – False

    PersonLos Is NULL – True

Solution :  In place of x.PersonLoc == personData. PersonLoc  use

((x.   PersonLoc == null && personData. PersonLoc == null) || (x. PersonLoc == personData. PersonLoc))

Example :

IEnumerable<Person> blocks = _db.Persons.Where(x => x.Name == personData.Name && ((x.   PersonLoc == null && personData. PersonLoc == null) || (x. PersonLoc == personData. PersonLoc)));

In LINQ To Entites,

• if you are using “x == null “ in where clause, It will be converted into x IS NULL in SQL.
• If  string y = null ; you are using  "x == y " in where clause, It will be converted into x = null in SQL.

Thanks for visiting, Please leave your comments, if it helps you

C# LINQ : SingleOrDefault() Vs. FirstOrDefault()

LINQ provides the operators like Single (), SingleOrDefault(), First() and FirstOrDefault(), they select a specific record from collection.SingleOrDefault() and FirstDefault() returns NULL or Default Value if there is no matching record found.

here is a list of customers :

 List<Customer>Customers = new List<Customer>

            {

                 new Customer { CustomerID = 1, Name= “Herry” },

                 new Customer { CustomerID = 2, Name=”Tom” }

            };

Select record by using FirstOrDefault() method

  Customer customer = Customers.FirstOrDefault(x => x.CustomerID = 1)

Select record by using SingleOrDefault() method

                Customer customer = Customers.SingleOrDefault(x => x.CustomerID = 2);

SingleOrDefault() vs FirstOrDefault()

• SingleOrDefault() throw exception if we have more than one matching records, and FirstOrDefault() returns the first matching record in sequence.
• Both SingleOrDefault() and  FirstOrDefault() return default value or NULL, If no matching record found.
• FirstOrDefault() generates the TSQL statement "SELECT TOP 1..." and SelectOrDefault() generates regular TSQL statement "SELECT ..”
• In Performance, FirstOrDefault() is usually faster than SingleOrDefault(). The FirstOrDefault() doesn’t care about record uniqueness and amount of record

Recommendations: if you sure, the query should result in at most a single result, you should use SingleOrDefault(), and on other side if query return any number of results, you should use FirstOrDefault() method.

Thanks for visiting!!

LINQ To EF : Error "The LINQ expression node type 'Invoke' is not supported in LINQ to Entities in entity framework"

In LINQ -to-EF query, First EF query is translated into SQL and when we are trying to translate some code into SQL and something is not supported in SQL and then this exception occurred.

"The LINQ expression node type 'Invoke' is not supported in LINQ to Entities in entity framework"

    public List<PersonData> GetAll()

        {

             return _db.Persons.Select(x => MapToPersonData(x)).ToList();

        }

internal static Func<Person, PersonData> MapToPersonData = m =>

    {

        if (m == null)

        {

            return null;

        }

        else

        {

            return new PersonData()

            {

              PersonID = m.PersonID,

              Name = m.Name,

              Location = m.Location.City +""+ m.Location.Street

            };

        }

    };

In above EF Query, the function MapToPersonData()  is being used to map the person entity into person data object and in this function, we are concatenating  of the city and Street string and the concatenating of string will not be supported in LINQ to entties, so the above error is occured.

What solution I found, we have to fetch data from database table in your local memory by calling .ToList()/.ToEnumerable() function and then we can apply logic on top of that similar like LINQ-To-Object 

       public List<PersonData> GetAll()

        {

           return _db.Persons.AsEnumerable().Select(x => MapToNameData(x)).ToList();

        }

  
Other entity framework related links: 

Entity Framework : Bulk Insert 

Basic Steps to improve EF query performance

Entity Framework 4.0: How to log SQL statements

Entity Framework : Create, Update and Delete

Entity Framework - Asynchronous Programming (async/await)

Entity Framework : Inner /Left Outer Joins

Entity Framework : Eager Loading of Multiple Levels of Entities by Using Include 

Entity Framework : Bulk Update

EF 4.0 : Managing Concurrency

Thanks for Visiting and please leave your comments, if it helps you.

C# IEnumerable vs IQueryable

The primary difference is that IEnumerable is great for working with in-memory collections, and IQueryable for a remote data source, like a database or web service.

IEnumerable : 
IEnumerable represent a forward collection of T and It has one GetEnumerater() method, from which you can iterate through collection.

• It does not support adding, removing objects on collection.
• It loads all object collection into local memory by using LINQ or entity framework before running query.
• It is best option for in-memory data source like object collections, if your IEnumerable query is being executed against external database, it would be network overhead and first loads whole entity data in local memory and then apply filter.

 public interface IEnumerable<out T> : IEnumerable

    {      

        IEnumerator<T> GetEnumerator();

    }

IQueryable

 IQueryable is similar like LINQ and it parse the expression tree and generates SQL querya nd execute the script against the external data source.

• It inherits the IEnumerable interface so it allows you to iterate over collection using ForEach statement.
• It is best option for out-memory data source like database and it send only the filtered data to client and reduce network overhead due to reducing record count.

public interface IQueryable<out T> : IEnumerable<T>

{

}

Difference between Select and SelectMany in LINQ

Select and SelectMany are projection operator and Select method is used to select a

value from collection and SelectMany method is used to select value from multiple

collection. 

In below example, we will see that Select will returns the collection of Player object and to read Player Name, we need one more For Each loop to read the Player object and

SelectMany will flatten the list into a single list of Players, so we need only one for-each

loop to get player name.

C# Example : 

List<Team> teams = new List<Team>();

Team team1 = new Team {

             Name = "USA",

             Players = new List<Player> {

                                     new Player {  PlayerName = "John" },

                                     new Player { PlayerName = "Peter" },

                                     new Player { PlayerName = "Steve" }

             } };

Team team2 = new Team

         {

             Name = "AUS",

             Players = new List<Player> {

                    new Player {  PlayerName = "Ricky" },

                    new Player { PlayerName = "McGraw" },

                    new Player { PlayerName = "Donald" }

             }

         };

teams.Add(team1);

teams.Add(team2);

Select : 

by using Select query. We need two for-each loop to retrieve the player name from a

team collection of arrays, because Select returns a collection of arrays.

C# Example : 

var resultSelect = teams.Select(e => e.Players);

foreach (var playerList in resultSelect)

         {

                foreach (var player in playerList)

             {                      Console.WriteLine(player.PlayerName);

             }

                Console.WriteLine();

         }

Output:

John

Peter

Steve

Ricky

McGraw

Donald

SelectMany: 

By using SelectMany query, we only need one for each loop to retrieve the player name

from team collection of array and SelectMany returns a one dimensional collection.

C# Example : 

     

      var resultSelectMany = teams.SelectMany(emp => emp.Players);

      foreach (var player in resultSelectMany)

         {

                Console.WriteLine(player.PlayerName);

         }

            Console.ReadKey();

     }

Output:

John

Peter

Steve

Ricky

McGraw

Donald

Thanks for visiting !!