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

Relationships in SQLServer and Entity Framework

Relationships in SQL Server and Entity

Table to map both Relational and LINQ to SQL objects depicted in image below:

 

A typical relationships like one-to-one ,one-to-Many,many-to-many are shown below in image:

 

 

 

Thanks

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

MVC Articles

Thanks.
Enjoy coding and reading.

Categories: LINQ , SQL Server

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

Difference between IEnumerable and IEnumerator

Difference between IEnumerable and IEnumerator

Hi Geeks,

 

 

Here are few points which I learnt about IEnumerable and IEnumerator.

1. IEnumerable uses IEnumerator internally.
2. IEnumerable doesnt know which item/object is executing.
3. Whenever we pass IEnumerator to another function ,it knows the current position of item/object.
4. Whenever we pass IEnumerable collection to another function ,it doesn't know the current position of item/object(doesn't know where I am)
5. IEnumerable have one method GetEnumerator()

IEnumerator have one Property current and two methods Reset and MoveNext.

In simple words: If you want to loop through with the collection one by one and you are not interested in the current cursor position then should opt 

Enumerable.Because code is simple and short.

And if you are keen to know the current position of object then should go for IEnumerate.

 

To know more about MVC please go through with given below link.

MVC Articles

Enjoy Coding and Reading

Categories: C#

Difference between prop and attr in JQuery.

Difference between prop and attr in JQuery?
Answer:

JQuery.attr()
Get the value of an attribute for the first element in the set of matched elements.
Whereas:
JQuery. Prop ()
Gets the value of a property for the first element in the set of matched elements.

What Attributes actually are
Attributes carry additional information about an HTML element and come in name=”value” pairs. You can set an attribute for a HTML element and define it when writing the source code.
For example:
<input id="txtBox" value="Jquery" type="text" readonly="readonly" />
As shown above, “id”, "type” and “value" are attributes of the input elements.

Property

Property is a representation of an attribute in the HTML DOM tree. Once the browser parses your HTML code, the corresponding DOM node will be created that is an object thus having properties.
In the above case, once the browser renders the input in the browser, other properties like align, alt, autofocus and baseURI are checked and so on, will be added as depicted in the following image.

Since attr() gives you the value of an element as it was defined in the HTML on page load. It is always recommended to use prop() to get the values of elements modified via JavaScript/jQuery in the browser at runtime. It always keeps the current state value.
Here we'll have a look at the example that also states the difference between both of them.
I have a HTML text box with some attributes as shown below:

If I run the following jQuery syntax then it will produce such results.

Now I've slightly changed the code and removed the read-only attribute as shown below in the image:

I run the application and see some attribute and property to understand the difference on fly.
Initially after running the application we have these attributes and properties of input text type as depicted in the image below.
Note: Kindly scroll down when you run the attached sample application to see the Value property using the Firebug tool of the Firefox browser.

Now I changed the value at runtime and see the attributes and properties. I've put Welcome jQuery in the textbox. Now see that the attribute value is still jQuery while the value property has been changed to Welcome JQuery.

The property always represents the current state while the attribute (except in old versions of IE) represents the initial state or is meant for HTML attributes since they are strictly defined. The attribute tells you nothing about the current state.

Reference MSDN:
for a checkbox (jquery 1.6+)
<input id="check1" checked="checked" type="checkbox" />
.attr('checked') //returns checked
.prop('checked') //returns true
.is(':checked') //returns true

Prop() method returns Boolean value for checked, selected, disabled, readOnly..and so on while attr returns defined string. So, you can directly use .prop("checked") in if condition.
SelectedIndex, tagName, nodeName, nodeType, ownerDocument, defaultChecked, and defaultSelected..and so on should be retrieved and set with the .prop() method. These do not have corresponding attributes and are only properties.
.attr() calls .prop() internally so .attr() method will be slightly slower than accessing them directly through .prop().

Question: What is the difference between .js and .min.js and vsdoc.js?

Answer: The jQuery library comes in the 2 versions Production and Deployment. The deployment version is also known as the minified version. So .min.js is basically the minified version of the jQuery library file. Both the files are the same as far as functionality is concerned. but .min.js is quite small in size so it loads quickly and saves bandwidth.

Question: How to select id that contains Meta Character.
Answer: If any element id (<li id="first-li" class="list">Sachin Kalia</li>) contains a meta character between the id then it should be resolved using the two backslashes (\\) as the prefix in the ID selector.

Thanks.

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

MVC Articles

Thanks

Enjoy Coding and Reading

Categories: JQuery

Cross-Site Scripting Attack in MVC4

Cross-Site Scripting Attack in MVC4

 

 

In this article we will explore Cross-Site Scripting in an MVC application. In general this is the most dangerous threat by hackers.

Cross-Site Scripting is a kind of security feat. An attacker inserts malicious code into a web page or a storage database. XSS in itself is a threat that is brought by the internet security weaknesses of client-side scripting languages.
There are certain scenarios where it could fit like: If an attacker posts a malicious script that he can cause the browser to execute, this script is executed in the context of the victim's session, essentially enabling the attacker to do anything he wants to the DOM, including showing fake login dialogs or stealing a cookie.
There could be various ways to inject malicious code such as:

• Malicious Attack with login Dialog
• Vulnerable code segment inserted
• Query String Message
• HTML markup Passed in TextBox

Here I have created a MVC application and tried to insert some encoded HTML as shown in the image below.
Kindly hit the following URL : http://localhost:64175/Xss/Create

As soon as I click on the create button it shows in yellow an error screen as depicted in the image below:

MVC is smart enough to deal with such threats and prevents cross site attacks, this is one advantage of MVC because in case you forgot to handle this.
In case you want the user to submit HTML markups in the address then you can disable this prevention using [ValidateInput(false)].

This allows you to store values to the database using Entity Framework.
Now the output will be like this due to by default Razor will encode the HTML markups.

To convert HTML encoded to markup use the @Html.Raw helper method. Refer to the image shown below.

Now the output will be like this. Hit the following URL: http://localhost:64175/Xss/EmpDetails

Until here we have almost disabled all the security points and are about to enter a highly vulnerable string and a possible way to hack a document cookie and alert.

<img onmouseover=alert(1) src="/Images/Clickme.jpg" onmouseout=alert(document.cookie) >

As soon as you click it prompts you with an alert as shown below:

To prevent this use the Microsoft Anti-Cross Site Scripting Library (AntiXSS) and set it as your default HTML encoder.
How do you prevent XSS? Use of the following rules strictly will help prevent most if not all XSS attacks in your application:

1. Ensure all of your output is HTML-encoded.
2. Don't allow user-supplied text to end up in any HTML element attribute string.
3. Prevent the use of Internet Explorer 6 by your application by checking Request.Browser as outlined at msdn.microsoft.com/library/3yekbd5b.
4. Understand your control's behaviour and whether it HTML encodes its output. If it doesn't, encode the data going to the control.
5. Use the Microsoft Anti-Cross Site Scripting Library (AntiXSS) and set it as your default HTML encoder.
6. Use the AntiXSS Sanitizer object (this library is a separate download and is addressed later in this article) to call GetSafeHtml or GetSafeHtmlFragment before saving HTML data to the database; don't encode the data before saving.

Kindly find a source code for XSS. You can also visit DotnetPiper.com to understand more about MVC and jQuery.
Note: select all SimpleMemberShip and unzip into single Extract to SimpleMemberShip.
Please replace the packages and bin folder to SimpleMemberShip after unzipping. Refer to the image below:

Download code from here

Cross-Site Scripting Attack in MVC4

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

Thanks

Enjoy Coding and Reading

Categories: MVC