Learn Linq Queries

In this blog post we are going to discuss some basic linq queries for learning purpose.  So let's discuss about linq. Linq( Language Integrated Queries) are SQL-like C# queries that can be used to manipulate collections of objects.

Where Clause 
The following sample shows how to retrieve a list of City where the Name contains "Lucknow".

using (dbContext db = new dbContext())
{
 var query_where1 = from a in db.Cities
                    where a.Name.Contains("Lucknow")
                    select a;
 foreach (var a in query_where1)
 {
  System.Console.WriteLine("City Name "+a.Name);
 }
}

The following sample shows how to retrieve a list of Cities where StateId is 10

using (dbContext db = new dbContext()
{
 var query1 = from a in db.Cities                    
                    where a.StateId == 10
                    select a;
 
 foreach (var a in query1)
 {
  System.Console.WriteLine("City Name"+a.Name);
 }
}

Join and Where Clause
The following example shows how to retrieve the First Name and the LastName where the First Name contains “Ram” and the contact LastName contains “Kumar”

using (dbContext db = new dbContext())
{
 var query2= from c in db.ContactSet
                    join a in db.AccountSet
                    on c.ContactId equals a.PrimaryContactId.Id
                    where a.FirstName.Contains("Ram")
                    where c.LastName.Contains("Kumar")
                    select new
                    {
                     first_name = a.FirstName,
                    lastname_name = c.LastName
                    };
 
 foreach (var c in query2)
 {
  System.Console.WriteLine("First Name: " +
   c.FirstName +   "Last Name: " +
   c.LastName);
 }
}

Use the Distinct Operator
The following example shows how to get a distinct list of contact last names.

using (dbContext db = new dbContext())
{
 var query_distinct = (from c in db.ContactSet
                       select c.LastName).Distinct();
 foreach (var c in query_distinct)
 {
  System.Console.WriteLine(c);
 }
}

Inner Join
The following example shows how to retrieve information about an Name and the contact listed .

using (dbContext db = new dbContext())
{
 var query3 = from c in db.ContactSet
                   join a in db.AccountSet
                  on c.ContactId equals a.PrimaryContactId.Id
                   select new
                   {
                    c.FullName,
                    c.Address1_City,
                    a.Name,
                    a.Address1_Name
                   };
 foreach (var c in query_join1)
 {
  System.Console.WriteLine("Name: " +
   c.Name +
   "contact: " +c.Address1_Name
   c.Address1_City+);
 }
}

Self Join
The following sample shows how to retrieve information about Users & Managers using Self Joint.

using (dbContext db = new dbContext())
{
 var query5 = from a in db.Users
                   join a2 in db.Users
                   on a.UserId.Id equals a2.ManagerId
 
                   select new
                   {
                    user_name = a.Name,
                    mamanger_name = a2.Name
                   };
 foreach (var c in query5)
 {
  System.Console.WriteLine(c.user_name + "  " + c.  mamanger_name);
 }
}

Multiple Joins
The following example shows how to get information from Users, Country and State

using (dbContext db = new dbContext())
{
 var query = from a in db.User
                   join c in db.Country
                   on a.CountryId.Id equals c.CountryId
                   join l in db.State
                   on c.CountryId equals l.CountryId
                   select new
                   {
                    contact_name = a.FullName,
                   country_name = c.Name,
                    state_name = l.StateName
                   };
 foreach (var c in query)
 {
  System.Console.WriteLine(c.contact_name +
   "  " +
   c.country_name +
   "  " +
   c.state_name);
 }
}

Join Using Entity Fields
The following example shows how to get information about accounts from a list

using (dbContext db = new dbContext())
{
 var list_join = (from a in db.Accounts
                  join c in db.ContactSet
                  on a.PrimaryContactId.Id equals c.ContactId
                  where a.Name == "Ram Ltd" &&
                  a.Address1_Name == "Ram Pharmaceuticals"
                  select a).ToList();
 foreach (var c in list_join)
 {
  System.Console.WriteLine("Account " + list_join[0].Name
      + " and it's primary contact "
      + list_join[0].PrimaryContactId.Id);
 }
}

Left Join 
The following sample shows a left join. A left join is designed to return parents with and without children from two sources. There is a correlation between parent and child, but no child may actually exist.

using (dbContext db = new dbContext())
{
 var query_join8 = from a in db.AccountSet
                   join c in db.ContactSet
                   on a.PrimaryContactId.Id equals c.ContactId
                   into gr
                   from c_joined in gr.DefaultIfEmpty()
                   select new
                   {
                    contact_name = c_joined.FullName,
                    account_name = a.Name
                   };
 foreach (var c in query_join8)
 {
  System.Console.WriteLine(c.contact_name + "  " + c.account_name);
 }
}
}

Use the Equals Operator 
The following sample shows how to retrieve a list of contacts where the FirstName is “Shivam”.

using (dbContext db = new dbContext())
{
 var query_equals1 = from c in db.ContactSet
                     where c.FirstName.Equals("Shivam")
                     select new
                     {
                      c.FirstName,
                      c.LastName,
                      c.Address1_City
                     };
 foreach (var c in query_equals1)
 {
  System.Console.WriteLine(c.FirstName +
   " " + c.LastName +
   " " + c.Address1_City);
 }
}

The following example shows how to retrieve a list of contacts where the First Name is not Shivam.

using (dbContext db = new dbContext())
{
 var query_ne2 = from c in db.ContactSet
                 where !c.FirstName.Equals("shivam")
                 select new
                 {
                  c.FirstName,
                  c.LastName,
                  c.Address1_City
                 };
 
 foreach (var c in query_ne2)
 {
  System.Console.WriteLine(c.FirstName + " " +
   c.LastName + " " + c.Address1_City);
 }
}

Use the Greater Than Operator 
The following example shows how to retrieve a list of contacts with an Anniversary date later than March 5, 2015.

using (dbContext db = new dbContext())
{
 var query = from c in db.ContactSet
                 where c.Anniversary > new DateTime(2015, 3, 5)
                 select new
                 {
                  c.FirstName,
                  c.LastName,
                  c.Address1_City
                 };
 
 foreach (var c in query)
 {
  System.Console.WriteLine(c.FirstName + " " +
   c.LastName + " " + c.Address1_City);
 }
}

Use the Skip and Take Operators 
The following sample shows how to retrieve just two records after skipping two records where the LastName is not “Parker” using the Skip and Take operators.

using (dbContext db = new dbContext())
{
 
 var query_skip = (from c in db.ContactSet
                   where c.LastName != "Parker"
                   orderby c.FirstName
                   select new
                       {
                        last = c.LastName,
                        first = c.FirstName
                       }).Skip(2).Take(2);
 foreach (var c in query_skip)
 {
  System.Console.WriteLine(c.first + " " + c.last);
 }
}

FirstOrDefault and SingleOrDefault Operators
The FirstOrDefault operator gives the first element of a sequence, or a default value if no element is found. The SingleOrDefault operator returns a single, specific element of a sequence, or a default value if that element is not found. The following sample shows how to use these operators.

using (dbContext db = new dbContext())
{
 
 Contact firstorcontact = db.ContactSet.FirstOrDefault();
 
 Contact singleorcontact = db.ContactSet
  .SingleOrDefault(c => c.ContactId == _contactId1);
 
 
 System.Console.WriteLine(firstorcontact.FullName +
  " is the first contact");
 System.Console.WriteLine("==========================");
 System.Console.WriteLine(singleorcontact.FullName +  " is the single contact");
}