ASP. Net WebAPI With. Net Core & Micro Services

Join Me in GuruGram(Gurgaon) in IKeva to learn

ASP. Net WebAPI With. Net Core & Micro Services

Warm Regards

Sachin Kalia

Categories: .Net Core , Angular2 , Entity Framework & LINQ , SQL Server , WCF and WebAPI

Implement Joins in Entity Framewok Using DB Context and LINQ

Implement Joins in Entity Framewok Using DB Context and LINQ

Excerpts from MSDN about LINQ

Language-Integrated Query (LINQ) is a set of features introduced in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic. LINQ introduces standard, easily-learned patterns for querying and updating data, and the technology can be extended to support potentially any kind of data store. Visual Studio includes LINQ provider assemblies that enable the use of LINQ with .NET Framework collections, SQL Server databases, ADO.NET Datasets, and XML documents.
In this article I am sharing how to use joins in Entity Framework with a DB context and a LINQ query.
I will use a sample application to demonstrate this feature.
We need to create DB Context object to understand the fact and advantages, so let's create a .edmx and dig into more details. Kindly follow the following procedure to create a DBContext. Create a sample MVC application and follow the procedure below.

1. Right-click on the Model folder and proceed as shown in the following screenshot:

Choose "Visual C#" => "ADO.NET Entity Data Model" then provide the name as you need to (for exampleNorthwind.edmx) as in the following:

Click "Add" then a new window will appear as in the following:

Select "Generate from database" and click "Next"; the following window will appear:

Click on "New Connection" and fill in the required details as shown in the following image to connect with the server (in this for example I have used "local DB Server").

Click on "Test Connection" to verify that the connection has been established.

Click "Ok" and proceed further.

Click "Next", the following window will appear:

Select the required database objects as per your needs and click on the "Finish" button.
An edmx file will be added into the solution as per the image show under the following:

These are the very basic steps to create an entity object from an existing database as shown above. Now let's proceed further and get to the core topic to use Joins with Entity Framework.
Now open the RegisterController class file and write my code in Action "Verify" also as shown in the following image:

You can see the code snippet in the above diagram states that we are using an inner join on two tables, Orders and OrderDetails, and return the result to the view (that is of type OrderModel having the following code snippet).

Now I run the sample application and press F5.

It's very simple in use, we just need to understand how to implement this. Hope it will be helpful somewhere someday. And I will contimue to post a few more articles related to MVC, Entity and many more.

A sample application is attached as a reference here

http://www.c-sharpcorner.com/UploadFile/97fc7a/implement-joins-in-entityframewok-using-db-context-and-linq/

To learn more about MVC please go to the following link.

MVC Articles

Thanks.
Keep coding and Stay Happy

Categories: Entity Framework & LINQ

Eager Loading and lazy Loading in Entity Framework: Easy Way to learn

Eager Loading with DBContext

Eager loading is the process, where a query for one type of entity also loads related entities as part of the query. Eager loading is achieved by use of the 'Include' method.

 

 

Below code snippet shows eager loading where Standard entity will also be loaded with Student entity using Include method.

 

Lazy Loading with DBContext

Lazy loading means delaying the loading of related data until you specifically request it. For example, in below code snippet, we first load all the students from the database then it will load address of particular student when we access StudentAddress entity.

 

 

To learn more about MVC please go to the following link.

MVC Articles

Thanks.
Enjoy coding and reading.

Categories: Entity Framework & LINQ , LINQ

Let Vs Into Keywords in LINQ

Let Vs Into Keywords in LINQ

This article shows two keywords of LINQ. These keywords are very helpful when working with a set of object collections using LINQ.

First I will go through the Into keyword and its use.

I used the following collection in this article, on which I've done actions in this article.

  1: List<Mobile> objMobileCollection = new List<Mobile>  
  2: {   
  3: new Mobile{Provider="Apple",ModelName="IPhone3",ModelNumber="I100",MemoryCard=4},  
  4: new Mobile{Provider="Apple",ModelName="IPhone4",ModelNumber="I101",MemoryCard=8},  
  5: new Mobile{Provider="Apple",ModelName="IPhone4S",ModelNumber="I102",MemoryCard=16},  
  6: new Mobile{Provider="Apple",ModelName="IPhone5",ModelNumber="I103",MemoryCard=32},  
  7: new Mobile{Provider="Samsung",ModelName="GalaxyS",ModelNumber="S001",MemoryCard=4},  
  8: new Mobile{Provider="Samsung",ModelName="GalaxyS2",ModelNumber="S002",MemoryCard=16},  
  9: new Mobile{Provider="Samsung",ModelName="GalaxyS3",ModelNumber="S003",MemoryCard=20},  
 10: new Mobile{Provider="Samsung",ModelName="Grand",ModelNumber="G001",MemoryCard=4},  
 11: new Mobile{Provider="Nokia",ModelName="LUMIA530",ModelNumber="N1",MemoryCard=8},  
 12: new Mobile{Provider="Nokia",ModelName="LUMIA730",ModelNumber="N2",MemoryCard=16},  
 13: new Mobile{Provider="Nokia",ModelName="LUMIA930",ModelNumber="N3",MemoryCard=20},  
 14: };  

Into keyword
The Into keyword allows creating a temporary variable to store the results of a group, join or select clause into a new variable.

  1: var resultSet = from mobile in objMobileCollection  
  2: group mobile by new { mobile.Provider } into mGroup  
  3: orderby mGroup.Key.Provider  
  4: select new
  5: {  
  6: Provider = mGroup.Key.Provider,  
  7: ModelName = mGroup.OrderBy(x => x.ModelName),  
  8: //ModelNumber = mGroup.OrderBy(x => x.ModelNumber),
  9: //MemoryCard = mGroup.OrderBy(x => x.MemoryCard),
 10: };  
 11: 

In the above query after applying Into on mobile groping it creates the type mGroup variable to apply the next filter or create a temporary variable “mGroup” for further operations.

As in the code below:

  1: foreach (var items in resultSet)  
  2: {  
  3: Console.WriteLine("{0} - {1}", items.Provider, items.ModelName.Count());  
  4: Console.WriteLine("------------------------------------------------");  
  5: foreach (var item in items.ModelName)  
  6: {  
  7: Console.WriteLine(item.Provider + "\t" + item.ModelName + "\t\t" + item.ModelNumber.Trim() + "\t" + item.MemoryCard);  
  8: }  
  9: Console.WriteLine();  
 10: }  
 11: Console.ReadLine();  
 12: 

Please have a look at the output as shown in the following image:

Let Keyword


The Let keyword permits you to store the result of the query that can be used further in subsequent queries.
It creates a new variable and initializes it with the result of the expression and can be used further in queries just the opposite of the Into keyword, which means you created a new variable and you also can use the previous variable so you can use both in the further operations.

  1: var resultSetLet = (from mobile in objMobileCollection  
  2: group mobile by new { mobile.Provider } into mGroup  
  3: //orderby mGroup.Key.Provider,mGroup.Key.MemoryCard
  4: let avgMemory = mGroup.Sum(x => x.MemoryCard) / mGroup.Count()  
  5: where avgMemory > 11  
  6: select new
  7: {  
  8: Provider = mGroup.GroupBy(x => x.Provider),  
  9: ModelName = mGroup.OrderBy(m => m.ModelName),  
 10: ModelNumber = mGroup.OrderBy(x => x.ModelNumber)  
 11: //MemoryCard = mGroup.OrderBy(x => x.MemoryCard),
 12: }).ToList();  
 13: 

Kindly refer to the image shown below for further reference:

Please have a look at the complete code shown below:

  1: var resultSetLet = (from mobile in objMobileCollection  
  2: group mobile by new { mobile.Provider } into mGroup  
  3: //orderby mGroup.Key.Provider,mGroup.Key.MemoryCard
  4: let avgMemory = mGroup.Sum(x => x.MemoryCard) / mGroup.Count()  
  5: where avgMemory > 11  
  6: select new
  7: {  
  8: Provider = mGroup.GroupBy(x => x.Provider),  
  9: ModelName = mGroup.OrderBy(m => m.ModelName),  
 10: ModelNumber = mGroup.OrderBy(x => x.ModelNumber)  
 11: //MemoryCard = mGroup.OrderBy(x => x.MemoryCard),
 12: }).ToList();  
 13: 
 14: foreach (var items in resultSetLet)  
 15: {  
 16: Console.WriteLine("{0} - {1}", items.Provider, items.ModelNumber.Count());  
 17: Console.WriteLine("------------------------------------------------");  
 18: foreach (var item in items.ModelNumber)  
 19: {  
 20: Console.WriteLine(item.Provider + "\t" + item.ModelName + "\t\t" + item.ModelNumber + "\t" + item.MemoryCard);  
 21: }  
 22: Console.WriteLine();  
 23: }  
 24: Console.ReadLine();  

 
Please have a look at the output as shown below in the image:





Note: Group by multiple columns in LINQ to SQL as shown in the image below:




This is the complete code used in this article.

  1: using System;
  2: using System.Collections.Generic;
  3: using System.Linq;
  4: using System.Text;
  5: 
  6: namespace LINQ_LetKeyword
  7: {
  8:     #region hiddencode
  9:     //class Employee
 10:     //{
 11:     //    public string Name { get; set; }
 12:     //    public string EmpID { get; set; }
 13:     //    public int Salary { get; set; }
 14: 
 15:     //}
 16:     #endregion
 17: 
 18:     class Mobile
 19:     {
 20:         public string Provider { get; set; }
 21:         public string ModelName { get; set; }
 22:         public string ModelNumber { get; set; }
 23:         public int MemoryCard { get; set; }
 24:     }
 25: 
 26:     class Program
 27:     {
 28:         static void Main(string[] args)
 29:         {
 30: 
 31:             
 32: 
 33:             List<Mobile> objMobileCollection = new List<Mobile> { 
 34:              new Mobile{Provider="Apple",ModelName="IPhone3",ModelNumber="I100",MemoryCard=4},
 35:              new Mobile{Provider="Apple",ModelName="IPhone4",ModelNumber="I101",MemoryCard=8},
 36:              new Mobile{Provider="Apple",ModelName="IPhone4S",ModelNumber="I102",MemoryCard=16},
 37:              new Mobile{Provider="Apple",ModelName="IPhone5",ModelNumber="I103",MemoryCard=32},
 38:              new Mobile{Provider="Samsung",ModelName="GalaxyS",ModelNumber="S001",MemoryCard=4},
 39:              new Mobile{Provider="Samsung",ModelName="GalaxyS2",ModelNumber="S002",MemoryCard=16},
 40:              new Mobile{Provider="Samsung",ModelName="GalaxyS3",ModelNumber="S003",MemoryCard=20},
 41:              new Mobile{Provider="Samsung",ModelName="Grand",ModelNumber="G001",MemoryCard=4},
 42:              new Mobile{Provider="Nokia",ModelName="LUMIA530",ModelNumber="N1",MemoryCard=8},
 43:              new Mobile{Provider="Nokia",ModelName="LUMIA730",ModelNumber="N2",MemoryCard=16},
 44:              new Mobile{Provider="Nokia",ModelName="LUMIA930",ModelNumber="N3",MemoryCard=20},
 45: 
 46:             };
 47: 
 48: 
 49:             var resultSet = from mobile in objMobileCollection
 50:                             group mobile by new { mobile.Provider, mobile.MemoryCard } into mGroup
 51:                             orderby mGroup.Key.Provider
 52:                             select new
 53:                             {
 54:                                 Provider = mGroup.Key.Provider,
 55:                                 ModelName = mGroup.OrderBy(x => x.ModelName),
 56:                                 //ModelNumber = mGroup.OrderBy(x => x.ModelNumber),
 57:                                 //MemoryCard = mGroup.OrderBy(x => x.MemoryCard),
 58:                             };
 59: 
 60:             foreach (var items in resultSet)
 61:             {
 62:                 Console.WriteLine("{0} - {1}", items.Provider, items.ModelName.Count());
 63:                 Console.WriteLine("------------------------------------------------");
 64:                 foreach (var item in items.ModelName)
 65:                 {
 66:                     Console.WriteLine(item.Provider + "\t" + item.ModelName + "\t\t" + item.ModelNumber.Trim() + "\t" + item.MemoryCard);
 67:                 }
 68:                 Console.WriteLine();
 69:             }
 70:             Console.ReadLine();
 71: 
 72: 
 73:             /////////////Var Keyword uses
 74: 
 75: 
 76:             //var resultSetLet = (from mobile in objMobileCollection
 77:             //                    group mobile by new { mobile.Provider } into mGroup
 78:             //                    //orderby mGroup.Key.Provider,mGroup.Key.MemoryCard
 79:             //                    let avgMemory = mGroup.Sum(x => x.MemoryCard) / mGroup.Count()
 80:             //                    where avgMemory > 11
 81:             //                    select new
 82:             //                    {
 83:             //                        Provider = mGroup.GroupBy(x => x.Provider),
 84:             //                        ModelName = mGroup.OrderBy(m => m.ModelName),
 85:             //                        ModelNumber = mGroup.OrderBy(x => x.ModelNumber)
 86:             //                        //MemoryCard = mGroup.OrderBy(x => x.MemoryCard),
 87:             //                    }).ToList();
 88:             
 89:             //foreach (var items in resultSetLet)
 90:             //{
 91:             //    Console.WriteLine("{0} - {1}", items.Provider, items.ModelNumber.Count());
 92:             //    Console.WriteLine("------------------------------------------------");
 93:             //    foreach (var item in items.ModelNumber)
 94:             //    {
 95:             //        Console.WriteLine(item.Provider + "\t" + item.ModelName + "\t\t" + item.ModelNumber + "\t" + item.MemoryCard);
 96:             //    }
 97:             //    Console.WriteLine();
 98:             //}
 99:             //Console.ReadLine();
100:             //var objresult = from emp in objEmployee
101:             //                let totalSalary = objEmployee.Sum(sal => sal.Salary)
102:             //                let avgSalary = totalSalary / 5
103:             //                where avgSalary > emp.Salary
104:             //                select emp;
105: 
106:             
107:         }
108:     }
109: }
110: 

I wish it will help you utilize both features at the best.
To learn more about MVC please go to the following link.


MVC Articles


Thanks.
Enjoy coding and reading.

Categories: Entity Framework & LINQ

Mapping of LINQ to SQL Object and Relational Object(Sql Server Object)

Mapping of LINQ to SQL Object and Relational Object(Sql Server Object)
 
The following table shows the relationship between the LINQ to SQL object model and the corresponding relational model.
The LINQ to SQL object model provides the fundamental elements for working with and managing
relational objects. It is via this model that a relational model is mapped to and expressed in the
developer’s programming language.
In the LINQ to SQL object model, database commands are not issued against the database directly. As a
developer, you simply change values and execute methods within the confines of the object model. LINQ
to SQL then translates those changes or methods into the appropriate SQL commands and funnels them
through to the database to be executed.
Typical and more described details are showing below in table to map both objects.

Relational Object	LINQ to SQL Object
Database	Data Context
Table	Entity class
Column	Class member
Foreign-key relationship	Association

Hope this little excerpt help to .Net freaks.
To know more about MVC kindly go through with the links given below:
· Smart Working With Custom Value Providers in ASP.Net MVC
· Invoke Action With Model Binders in MVC
· Extension Helpers Method in MVC
· Custom Button With TagBuilder Using MVC Razor Engine
· Precompiled Razor View Using RazorGenerator MVC and PreCompiledViewEngine in MVC 4
· RETURN MULTIPLE MODELS IN SINGLE VIEW IN MVC3
· CALL CONTROLLER ACTION METHOD FROM JQUERY USING AJAX
· EXECUTION ORDER OF FILTERS IN MVC 4 WITH PRACTICES: IMPORTANT FAQ
· MEANING OF SCAFFOLDING IN MVC
· REMOVE AMBIGUTY OF CONTROLLER NAMES IN MVC APPLICATION
· CUSTOM BUTTON WITH TAGBUILDER TECHNIQUE USING MVC RAZOR ENGINE
· CONVERSION HELPERS IN MVC RAZOR: VALIDATING POSTED DATA
Thanks
Stay Happy and Stay Coding

Categories: Entity Framework & LINQ

Generate LINQ, SQL queries with LINQPad

Generate LINQ, SQL queries with LINQPad
 


Excerpts from msdn about LINQ:
Language-Integrated Query (LINQ) is a set of features introduced in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic. LINQ introduces standard, easily-learned patterns for querying and updating data, and the technology can be extended to support potentially any kind of data store. Visual Studio includes LINQ provider assemblies that enable the use of LINQ with .NET Framework collections, SQL Server databases, ADO.NET Datasets, and XML documents.
In this article I am sharing how can we generate a LINQ query with a very facilitative tool LINQPad.
The most important fact about LINQPad is, it provides you lot of functionality to play around with tables within a LINQPad editor.
So let’s see how beneficial this is for .Net freaks.
A very initial look of LINQPad shown below:

If you notice there is an option to create a connection with the desired database. I will use Northwind database for my demonstration purpose, here are few sequential steps to make connection establish.
Click in Add connection a window will appear.

Choose “Default (LINQ to SQL) and click on Next button. A New window will appear, fill the required details to get connect with the desired database. As I’ve chosen default provider, (local) server, SQL Authentication option and passed the desired credentials.

Click on Test button and a dialog box will appear with “Connection Successful” message as depicted below:

Click on ok button and see the below image.

To move ahead after the successful connection, now turn is to run some query and see the benefits of this.
Before to run query into a LINQPad editor please have a look on the toolbar section and mouse over on each at least one(toolbar name is very easy to understand).

As you can see into just above window connection is still pointing to none, though connection has been made already, but the open editor is not pointing to any already made connection. Click on connection dropdown and select as your desired as mine is Northwind.

You can also write your query in many modes which .Net supports, See the below image.

I will run inner join query here using Orders and OrderDetails tables of Northwind database.
At very first time I will choose language as SQL and run the following SQL query with green button.
select od.Quantity ,o.OrderID from dbo.Orders as o inner join dbo.OrderDetails as od
on o.OrderID = od.OrderID
Output:

Now I run the following LINQ query into LINQPad editor and choose the Language option as C# Statement(s).
var Result = from o in Orders join od in OrderDetails on o.OrderID equals od.OrderID
select new { od.Quantity,o.OrderID};
I run the above query but nothing comes as result. The reason behind this is we have to use an extension method Dump() to print the result which is built in method of LINQPad.
Let’s run the above query again with Dump method.
var Result = from o in Orders join od in OrderDetails on o.OrderID equals od.OrderID
select new { od.Quantity,o.OrderID};
Result.Dump();
See the output:

There are also some inbuilt extension methods, Right click on any table and see the answer as below showing image.

You can download LINQPad form here http://www.linqpad.net/.
 
You can also have a look on MVC related article here:
Smart Working With Custom Value Providers in ASP.Net MVC
EXECUTION ORDER OF FILTERS IN MVC 4 WITH PRACTICES: IMPORTANT FAQ
Invoke Action With Model Binders in MVC
Extension Helpers Method in MVC
Custom Button With TagBuilder Using MVC Razor Engine
CALL CONTROLLER ACTION METHOD FROM JQUERY USING AJAX
EXECUTION ORDER OF FILTERS IN MVC 4 WITH PRACTICES: IMPORTANT FAQ
MEANING OF SCAFFOLDING IN MVC
REMOVE AMBIGUTY OF CONTROLLER NAMES IN MVC APPLICATION
Precompiled Razor View Using RazorGenerator MVC and PreCompiledViewEngine in MVC 4
So far so good.
Hoe you enjoyed this demonstration.
Keep coding and Be Happy

Categories: Entity Framework & LINQ

Expanding the Results View Will Enumerate the IEnumerable Using DB Context

Expanding the Results View Will Enumerate the IEnumerable Using DB Context

This article describes the most frequent terms confronted by software geeks during LINQ execution. Let's understand the term “results view will enumerate the ienumerable”. Initially it may be slightly confusing for those who are new to LINQ and DB Context terminology but after this demonstration they may be able to understand this term. This entire process is also well known as deferred execution and immediate execution in LINQ.




You can also have a look at the MVC related articles here:

1. Custom Button With TagBuilder Using MVC Razor Engine
2. Smart Working With Custom Value Providers in ASP.NET MVC
3. Invoke Action With Model Binders in MVC
4. Extension Helpers Method in MVC
5. Precompiled Razor View Using RazorGenerator MVC and PreCompiledViewEngine in MVC 4
6. RETURN MULTIPLE MODELS IN SINGLE VIEW IN MVC3

 
Let's proceed step-by-step to understand the real facts.

Step 1: I've chosen already a built application of MVC that deals with entities (DB context). When I first run an application it reaches to the code segment as shown in the following depicted image:

Step 2: Execute it further and see the result variable, it prompts you with “expanding the results view will enumerate the ienumerable”.

Or you need to iterate over it to get the current value from collection.
Step 3: You can make it enumerable after converting to a list type with the help of the .ToList() method. This is also known as immediate execution in LINQ.
Kindly have a look at the more descriptive image shown below:

I hope you enjoyed this demonstration. A sample application is attached as reference.


I hope you enjoyed and that it may help you down the line.Download the related code from the link given below:
Expanding the Results View Will Enumerate the IEnumerable Using DB Context
Cheers .Net

Categories: Entity Framework & LINQ