How to Use Entity Framework SelectMany ?

What is SelectMany ?

1. SelectMany Operator is part of the projection query operator supported by Linq.

                        What is Projection ?
                                  - operation of transforming an object into a new form that often consists
                                      only of those properties that will be subsequently used
                                  - e.g:  select , SelectMany

2. SelectMany operator is mainly used for flattening out the hierarchy of collections into one single
    collection of objects.

3. It merges each item into a Single Sequence that gets returned by the query results.

Let's Try with Working Example.

Scenario 1: 

Linq to Object

I have created a Console application like below by using visual studio 2010.

Entity Framework Method Based Syntax

 public class Program
    {
        static void Main(string[] args)
        {
            var fruits = new List<string>() { "Orange Apple Peach Plum Banana" };

            var query = fruits.SelectMany(f => f.Split(' '));

            foreach (string s in query)
              {
                   Console.WriteLine(s);
               }
         }
    }

Result

Entity Framework Query Based Syntax

We can write above code as below for giving Same Result as above.

public class Program
    {
        static void Main(string[] args)
        {
            var fruits = new List<string>() { "Orange Apple Peach Plum Banana" };

            var query = from f in fruits
                              from word in f.Split(' ')
                              select word;

              foreach (string s in query)
              {              
                 Console.WriteLine(s);
              }
       }
    }

Note : The Result of the above query is Exactly the Same,but the Query Based Syntax is much
               easier to follow and more readable.


Scenario 2: 

Linq To SQL

You can use SelectMany  with Linq To SQL as below.

Models

  public class Appointment 
    {
        public string AppointmentId { get; set; }
public virtual Provider Provider { get; set; }
public virtual IList<Allocation> Allocations { get; set; }
    }

   public class Allocation 
     {
       public Guid AllocationId { get; set; }
       public virtual Appointment Appointment { get; set; }
     }

  public class Provider
    {
       public Guid Id { get; set; }
       public string Key { get; set; }
       public virtual ICollection<Appointment> Appointments { get; set; }
    }

Entity Framework Method Based Syntax


 public Allocation GetAllocationDetails(string providerKey, Guid allocationId)
        {
            var catalog = new DataCatalog();

            var allocation =
                (catalog.Appointments.Where(a => a.Provider.Key == providerKey)
                .SelectMany(l => l.Allocations.Where(b => b.AllocationId == allocationId)))
                .FirstOrDefault();

            return allocation;
        }


Entity Framework Query Based Syntax

    public Allocation GetAllocationDetails(string providerKey, Guid allocationId)
        {
            var catalog = new DataCatalog();

            var allocation = (from a in catalog.Appointments
                                    where a.Provider.Key == providerKey
                                    from l in a.Allocations
                                    where l.AllocationId == allocationId
                                    select l).FirstOrDefault();

            return allocation;
        }

Both methods are giving Same Result as below.

Conclusion

• So in Linq there are 2 ways to write queries.
• One is Query Based Syntax and other is Method Based Syntax.
• Who is coming from T-Sql environment are more familiar with Query Based Syntax.Its easy to understand and readability is high.
• Method Based Syntax is suitable for more advance users who is having very good knowledge about Entity frame work Extension methods.
• But both are giving Same Result at the end.

I hope this helps to You.Comments and feedback greatly appreciated.



Happy Coding.


May Be Use Full To You :

How to Improve Performance of Entity Framework Query (over 400 times)?
How to Convert Linq Entity Query into T-SQL ?
How to use Asp.Net MVC TempData Properly ?