The conceptual data model schema is expressed in the Conceptual Schema Definition Language (CSDL) ,the actual storage model is expressed in the Storage Schema Definition Language (SSDL), and the mapping in between is expressed in the Mapping Schema Language (MSL).
Lets see how to create a simple LINQ to entity application.
Step 1:Create a console application as follows-
Step 2:Add a new Item "ADO.NET entity data model"
Step 3:Click Next and choose "Generate from database"
Step 4:Choose a existing connection or click new connection-
Step 5:Choose the dabase object as follows-
Step 6:After you click on the Finish button the following two files will be added to the project: Northwind.edmx and Northwind.designer.cs. The first file holds the model of the entities including the entity sets, entity types, conceptual models, and the mappings. The second one is the code for the model which defines the ObjectContext of the model.
Step 7:If you open the file, Northwind.Designer.cs (you need to switch from the Model Browser to Solution Explorer to open this file), you will find that the
following classes have been generated for the project:
public partial class NorthwindEntities : ObjectContext
public partial class Product : EntityObject
public partial class Category : EntityObject
public partial class Current_Product_List : EntityObject
In the above four classes, the NorthwindEntities class is the main conduit through which we'll query entities from the database as well as apply changes back to it. It contains various flavors of types and constructors, partial validation methods, and property members for all of the included tables. It inherits from the ObjectContext class which represents the main entry point for the LINQ to Entities framework.
The next two classes are for the two tables that we are interested in. They implement the EntityObject interface. This interface defines all of the related property changing and property changed event methods which we can extend to validate properties before and after the change.
The last class is for the view. This is a simple class with only two property members. Because we are not going to update the database through this view it doesn't define any property change or changed event method.
Querying records:
To query your records you can do any one of three(Lambda Expression,Query expression with var keyword,query expression with proper datatype) in following approach.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace LINQConsoleApp
{
class Program
{
static void Main(string[] args)
{
NORTHWINDEntities objNORTHWINDEntities = new NORTHWINDEntities();
var LambdaExpOutput = objNORTHWINDEntities.Products.Where(p => p.Category.CategoryName.Equals("Beverages")
p.Category.CategoryName.Equals("Condiments"))
.OrderByDescending(p => p.ProductName).ThenBy(p=>p.QuantityPerUnit)
.Select(p=> new {p.ProductName,p.QuantityPerUnit,p.Category.CategoryName});
var LinqVarOutput = from p in objNORTHWINDEntities.Products
where p.Category.CategoryName == "Beverages"
p.Category.CategoryName == "Condiments"
orderby p.ProductName, p.QuantityPerUnit descending
select new { p.ProductName, p.QuantityPerUnit, p.Category.CategoryName };
IEnumerable<Product> LinqOutput = from p in objNORTHWINDEntities.Products
where p.Category.CategoryName == "Beverages"
p.Category.CategoryName == "Condiments"
orderby p.ProductName descending, p.QuantityPerUnit
select p;
Console.WriteLine("There are {0} LambdaExpOutput Items", LambdaExpOutput.Count());
Console.WriteLine("There are {0} LinqVarOutput Items", LinqVarOutput.Count());
Console.WriteLine("There are {0} LinqOutput Items", LinqOutput.Count());
Console.WriteLine("--------------------------------------------------------------------------");
int maxLimit = 0;
foreach (var p in LambdaExpOutput)
{
if (maxLimit == 3)
{
break;
}
Console.WriteLine("Product Name :{0} QuantityPerUnit:{1} CategoryName:{2}", p.ProductName, p.QuantityPerUnit, p.CategoryName);
maxLimit++;
}
Console.WriteLine("--------------------------------------------------------------------------");
maxLimit = 0;
foreach (Product p in LinqOutput)
{
if (maxLimit == 3)
{
break;
}
Console.WriteLine("Product Name :{0} QuantityPerUnit:{1} CategoryName:{2}", p.ProductName, p.QuantityPerUnit, p.Category.CategoryName);
maxLimit++;
}
Console.ReadLine();
}
}
}
The output will be as follows-
Updating records
To change the product name of 3rd element you can do as follows-
NORTHWINDEntities objNORTHWINDEntities = new NORTHWINDEntities();
Product objProduct = LinqOutput.ElementAtOrDefault(2);
if (objProduct != null)
{
objProduct.ProductName = objProduct.ProductName + "_A";
objNORTHWINDEntities.SaveChanges();
}
Inserting records
To add a new record you can do as follows-
NORTHWINDEntities objNORTHWINDEntities = new NORTHWINDEntities();
Product objNewProduct = new Product { ProductName = "My Product" };
objNORTHWINDEntities.Products.AddObject(objNewProduct);
objNORTHWINDEntities.SaveChanges();
Deleting records
To delete the record you can do as follows-
NORTHWINDEntities objNORTHWINDEntities = new NORTHWINDEntities();
IQueryable<Product> productsToDelete =from p in objNORTHWINDEntities.Products
where p.ProductName == "My Product"
select p;
if (productsToDelete.Count() > 0)
{
foreach (var p in productsToDelete)
{
objNORTHWINDEntities.DeleteObject(p);
Console.WriteLine("Deleted product {0}", p.ProductID);
}
objNORTHWINDEntities.SaveChanges();
}
View SQL statements
To view the generated SQL statements used by LINQ to Entities queries.
NORTHWINDEntities objNORTHWINDEntities = new NORTHWINDEntities();
IQueryable<product> productsToDelete = from p in objNORTHWINDEntities.Products
where p.ProductName == "My Product"
select p;
Console.WriteLine(productsToDelete.ToTraceString());
Console.ReadLine();
Join n Query
When you don't have any foreign key relationship between tables it is very useful to use join query as follows
var categoryProducts = from c in objNORTHWINDEntities.Categories
join p in objNORTHWINDEntities.Products
on c.CategoryID equals p.CategoryID
into productsByCategory
select new
{
c.CategoryName,
productCount = productsByCategory.Count()
};
foreach (var cp in categoryProducts)
{
Console.WriteLine("There are {0} products in category {1}",
cp.productCount, cp.CategoryName);
}
Console.ReadLine();
Querying a view is the same as querying a table.
No comments:
Post a Comment