protected void Page_Load(object sender, EventArgs e)
{
var _region = "OR";
var _city = "Elgin";
using (var ctx = new EF360Context())
{
// Demonstate 'Query Syntax'
var querySyntax = (from c in ctx.Customers
where c.Region == _region &&
c.City != _city
orderby c.CompanyName
select c).Skip(1).Take(1);
//select c).Take(1);
ctx.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
gvQuerySyntax.DataSource = querySyntax.ToList();
gvQuerySyntax.DataBind();
//Same query using 'Method Syntax' (extension methods)
// with lamda expressions
var methodSyntax =
ctx.Customers.Where(c => c.Region == _region &&
c.City != _city)
.OrderBy(c => c.CompanyName).Skip(1).Take(1);
gvMethodSyntax.DataSource = methodSyntax.ToList();
gvMethodSyntax.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
// Grouping Operation - Query Syntax
// Number of Products in each Category
using (var ctx = new EF360Context())
{
// Perform operation with query syntax
var query = from x in ctx.Products
where x.UnitPrice >= 20
group x by x.CategoryID
into g
select new
{
Category = g.Key,
CateogryCount = g.Count()
};
gvGroupingQuerySyntax.DataSource = query.ToList();
gvGroupingQuerySyntax.DataBind();
// Grouping Operation - Method Syntax
// Perform operation with fluent method syntax
var groupingExample =
ctx.Products.Where(p => p.UnitPrice >= 20)
.GroupBy(p => p.CategoryID)
.Select(g => new { Category = g.Key, CateogryCount = g.Count() });
gvGroupingMethodSyntax.DataSource = groupingExample.ToList();
gvGroupingMethodSyntax.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
var customerId = "LONEP";
using (var ctx = new EF360Context())
{
// Retrieves proxy object that encapsulates customer
var targetEntity = ctx.Customers.Where(x => x.CustomerID == customerId).ToList();
//Dynamically created type from System.Data.Entity.DynamicProxies -- typeName and hash
var proxyName = targetEntity.Single().ToString();
// Determine if entity is a proxy object
var isProxyType = ObjectContext.GetObjectType(targetEntity.GetType()) != typeof(Customer);
// Proxy object is not created if use 'new' operator to instantiate entity object
var nonProxyCustomer = new Customer {
CustomerID = "abcde", CompanyName = "XYZ"
};
// Proxy object is created
// Create() does not add or attach the entity to the context
var proxyCustoner = ctx.Customers.Create <Customer>();
// Disable proxy creation, especially for services.
// But, impacts lazy loading and change tracking.
ctx.Configuration.ProxyCreationEnabled = false;
var proxyDisabledCustoner = ctx.Customers.Create <Customer>();
ctx.Configuration.ProxyCreationEnabled = true;
gvQuery.DataSource = targetEntity.ToList();
gvQuery.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
var _region = "OR";
var _city = "Elgin";
using (var ctx = new EF360Context())
{
//Same query using 'Method Syntax' with different order
var oregonCustomers =
ctx.Customers.Where(x => x.Region == _region && x.City != _city)
.GroupBy(x => x.Region);
var result = oregonCustomers.Include("Regions").First();
//.Include(x => x.Orders)
// .Select(x => new { x.CustomerID, x.CompanyName, x.Orders.FirstOrDefault(y => y.OrderID > 1).OrderID });
gvInclude.DataSource = result.ToList();
gvInclude.DataBind();
//Same query using 'Method Syntax' with different order
var oregonCustomers2 =
ctx.Customers.Include(x => x.Orders).Where(c => c.Region == _region && c.City != _city)
.OrderBy(c => c.CompanyName)
.ThenBy(c => c.City)
.Select(x => new { x.CustomerID, x.CompanyName, x.Orders.FirstOrDefault(y => y.OrderID > 1).OrderID });
gvInclude.DataSource = oregonCustomers2.ToList();
gvInclude.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
// Aggregation Operation (High/Low/Average price for each category)
using (var ctx = new EF360Context())
{
//-- Compiler infrers type
var query =
from p in ctx.Products
group p by p.Category.CategoryID into g
// Order from most to least
orderby g.Count() descending
//-- 'Select new {}' tells compiler to build anon type
//-- Anon type becomes our projection, or result set.
select new
{
//-- Demonstrate 'aggegrate query operators'
TotalPrice = g.Sum(p => p.UnitPrice),
LowPrice = g.Min(p => p.UnitPrice),
HighPrice = g.Max(p => p.UnitPrice),
AveragePrice = g.Average(p => p.UnitPrice)
};
gvAggregationOperation.DataSource = query.ToList();
gvAggregationOperation.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new EF360Context())
{
// Will retrieve only records which contain records in all three product tables.
// Note in designer how both EmployeeFinacial and EmployeePersonal are both mapped to Employees
var query = from emp in ctx.Employees
orderby emp.LastName descending
select new
{
emp.EmployeeID,
emp.FirstName,
emp.LastName,
emp.HireDate,
emp.PostalCode,
// Information from EmployeeFinancial Table
emp.CreditScore,
emp.CorporateRiskScore,
// Information from EmployeePersonal Table
emp.SSN
};
gvEntitySplitting.DataSource = query.ToList();
gvEntitySplitting.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new EF360Context())
{
Carrier carrier = null;
if (!Page.IsPostBack)
{
ConcurrencyResolutionType[] concurrencyResolution =
{
ConcurrencyResolutionType.ClientWins,
ConcurrencyResolutionType.ServerWins,
ConcurrencyResolutionType.MergeResults
};
string[] carrierRatings = { "A", "B", "C", "D" };
ddlResolution.DataSource = concurrencyResolution;
ddlCarrierRating.DataSource = carrierRatings;
ddlResolution.DataBind();
ddlCarrierRating.DataBind();
carrier = ctx.Carriers.Single(x => x.CarrierId == 1);
}
else
{
// Retrieve concurrency resolution type
var concurrencyResolution =
(ConcurrencyResolutionType)
Enum.Parse(typeof(ConcurrencyResolutionType), ddlResolution.SelectedValue);
// Retrive updated carrier rating
var carrierRating = ddlCarrierRating.SelectedValue;
// Get current record and update carrier rating
carrier = ctx.Carriers.Single(x => x.CarrierId == 1);
carrier.Rating = carrierRating;
// Emulate another using changing record while you have it open.
// Purposely update the underlying Carrier table so that the RowVersion value changes
ctx.Database.ExecuteSqlCommand(@"UPDATE dbo.Carriers SET rating = 'F' WHERE CarrierId = 1");
// Instantiate ConcurrencyClassHelper
var concurrencyClassHelper = new ConcurrencyHelperClass
{
ConcurrencyResolutionTypeValue = concurrencyResolution
};
concurrencyClassHelper.SaveChangesWithConcurrency(ctx);
}
lblId.Text = carrier.CarrierId.ToString();
lblName.Text = carrier.Name;
lblAbbrev.Text = carrier.Abbreviation;
lblRating.Text = carrier.Rating;
lblMonths.Text = carrier.ActiveMonthAssociated.ToString();
lblRowVersion.Text = carrier.RowVersion.ToString();
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new EF360Context())
{
// var query2 = from l in ctx.LocalTaxAuditSet
// select l;
// //-- Now, StateTaxAudit entity "inherits" the TaxAudit entity.
// //-- Retrieve all StateTaxAudit records, which includes associated
// //-- record from TaxAudit, without referring to TaxAudit, as
// //-- SalesTaxAudit inherits from TaxAudit
// var query = from stateAudit in ctx.TaxAudit.OfType<StateTaxAudit>()
// orderby stateAudit.AuditDate
// select new
// {
// //-- Properties from Tax Audit
// stateAudit.AuditDate,
// stateAudit.AuditReason,
// stateAudit.Adjustment,
// stateAudit.Audtor,
// //-- Properties from State Tax Audit
// stateAudit.State,
// stateAudit.Agency,
// stateAudit.SalesUseTax
// };
// gvBefore.DataSource = query;
// gvBefore.DataBind();
//}
//using (NorthwindEFEntities ctx = new NorthwindEFEntities())
//{
// //-- Now, FederalTaxAudit entity "inherits" the TaxAudit entity.
// //-- Retrieve all FederalTaxAudit records, which includes associated
// //-- record from TaxAudit, without referring to TaxAudit, as
// //-- FederalTaxAudit inherits from TaxAudit
// var query = from federalAudit in ctx.TaxAudit.OfType<FederalTaxAudit>()
// orderby federalAudit.AuditDate
// select new
// {
// //-- Properties from Tax Audit
// federalAudit.AuditDate,
// federalAudit.AuditReason,
// federalAudit.Adjustment,
// federalAudit.Audtor,
// //-- Properties from Federal Tax Audit
// federalAudit.IRSDistrict,
// federalAudit.TaxYear,
// federalAudit.DIFScore
// };
// gvAfter.DataSource = query;
// gvAfter.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new EF360Context())
{
ctx.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
var customerId = "GREAL";
var customerId2 = "HUNGO";
//Retrieve "Great Lakes Food Market" in "OR" customers
var GreatlakesFoodMarket = ctx.Customers.Where(x => x.CustomerID == customerId).ToList();
//Find "Great Lakes Food Market" in "OR" customers
//Because, we query against DbContext, we automatically query database, despite
//fact that "GreatlakesFoodMarket" is already in memory. Results in unnecessary
//round-trip to database.
GreatlakesFoodMarket = ctx.Customers.Where(x => x.CustomerID == customerId).ToList();
//Use DbSet.Find() method to find entity.
//Will (1) search context first, and, if not found, then (2) search database
//Must search by primary key of entity
var Greal = ctx.Customers.Find(customerId);
//Next find, an entity (Hungry Owl All-Night Grocers, ID=HUNGO), which is not
//loaded in DbContext instance
var Hungo = ctx.Customers.Find(customerId2);
//Add new customer to context, but do not commit to data store
ctx.Customers.Add(new Customer
{
CustomerID = "Customer1234",
Address = "1234 Main Street",
City = "Dallas",
Region = "TX",
CompanyName = "Hungry Shark" + new Random().Next(100),
});
//Find() will locate new entity in context
var newCustomer = ctx.Customers.Find("Customer1234");
//Example of deleting entity from Context by using Remove() method.
//Marks entity for deletion and removes when SaveChanges() called.
//Removes from context without calling database.
var deletedCustomer = ctx.Customers.Remove(newCustomer);
ctx.SaveChanges();
//var customers = ctx.Customers.Where(x => x.Region == "OR").ToList();
//gvFindingEntities.DataSource = customers.ToList();
//gvFindingEntities.DataBind();
}
}
public void SaveChangesWithConcurrency(EF360Context ctx)
{
try
{
ctx.SaveChanges();
}
catch (DbUpdateConcurrencyException ex)
{
ResolveConcurrencyConflicts(ex);
SaveChangesWithConcurrency(ctx);
}
}
protected void Page_Load(object sender, EventArgs e)
{
// Keep in mind: Querying EF conceptual model, not the database
using (var ctx = new EF360Context())
{
//// Retrieve data from stored procedure setup as a Function Import
//var product = ctx.ComplexTypeSproc("GREAL");
//// Bind result to GridView control
//gvComplex.DataSource = product;
//gvComplex.DataBind();
}
}
private void UpdateMainContext(string id)
{
// Generate transaction across multiple contexts
using (var ctx = new EF360Context())
{
// Update Code
var customer = ctx.Customers.SingleOrDefault(c => c.CustomerID == id);
// Modify postal code
customer.PostalCode = (int.Parse(customer.PostalCode) + 1).ToString();
ctx.SaveChanges();
}
}
protected void Page_Load(object sender, EventArgs e)
{
//Simple Set Operation (Customers in both OR and WA)
using (var ctx = new EF360Context())
{
var orCustomers = ctx.Customers.Where(c => c.Region == "OR");
var waCustomers = ctx.Customers.Where(c => c.Region == "WA");
var queryUnion = orCustomers.Union(waCustomers);
gvSetOperation.DataSource = queryUnion.ToList();
gvSetOperation.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
// Keep in mind: Querying EF conceptual model, not the database
using (var ctx = new EF360Context())
{
var result = ctx.GetUnitPricing("GREAL");
// Retrieve data from stored procedure setup as a Function Import
// This query returns all columns from the Product table
//var result = ctx.GetComplexTypeMethod("GREAL");
//gvTopTen.DataSource = result.ToList();
//gvTopTen.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
var _region = "OR";
var _city = "Elgin";
using (var ctx = new EF360Context())
{
//Same query using 'Method Syntax' with different order
var query1 =
ctx.Customers.Where(c => c.Region == _region && c.City != _city)
.OrderBy(c => c.CompanyName)
.ThenBy(c => c.City)
.Select(c => new { c.CompanyName, c.Region, c.City });
gvQuery1.DataSource = query1.ToList();
gvQuery1.DataBind();
//Same query using 'Method Syntax' with different order
var query2 =
ctx.Customers.OrderBy(c => c.CompanyName)
.ThenBy(c => c.City)
.Where(c => c.Region == _region && c.City != _city)
.Select(c => new { c.CompanyName, c.Region, c.City });
gvQuery2.DataSource = query2.ToList();
gvQuery2.DataBind();
//Same query using 'Method Syntax' with different order
var query3 =
ctx.Customers.Select(c => new { c.CompanyName, c.Region, c.City })
.Where(c => c.Region == _region && c.City != _city)
.OrderBy(c => c.CompanyName)
.ThenBy(c => c.City);
gvQuery3.DataSource = query3.ToList();
gvQuery3.DataBind();
//Same query using 'Method Syntax' with different order
var query4 =
ctx.Customers.Where(c => c.Region == _region && c.City != _city)
.Select(c => new { c.CompanyName, c.Region, c.City })
.OrderBy(c => c.CompanyName)
.ThenBy(c => c.City);
gvQuery4.DataSource = query4.ToList();
gvQuery4.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
// Retrieve management employees only
using (var ctx = new EF360Context())
{
// Use the OfType<>() method to filter on type,
// without using Where Clause
var query = from supplier in ctx.Suppliers.OfType <PreferredSupplier>()
orderby supplier.CompanyName
select new
{
supplier.CompanyName,
supplier.ContactName,
supplier.City,
supplier.Country,
supplier.Phone,
//-- Properties from derived
//-- PreferredSupplier Type
supplier.VolumeDiscountPercent,
supplier.PartnerDiscount,
supplier.ShippingDiscount
};
gvManagement.DataSource = query.ToList();
gvManagement.DataBind();
}
//-- Retrieve all employees
using (var ctx = new EF360Context())
{
//-- Use the OfType<>() method to filter on type,
//-- without using Where Clause
var query = from supplier in ctx.Suppliers.OfType <Supplier>()
orderby supplier.CompanyName
select new
{
supplier.CompanyName,
supplier.ContactName,
supplier.City,
supplier.Country,
supplier.Phone,
};
gvEmployees.DataSource = query.ToList();
gvEmployees.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new EF360Context())
{
//-- Create new employee of type "ManagementEmployee"
//-- Is separate type, but stored in employee table
var supplier = new PreferredSupplier
{
CompanyName = "Microsoft",
ContactName = "Brian Moore" + new Random().Next(100),
City = "Seattle",
PostalCode = new Random().Next(10000).ToString(),
Country = "United States",
Phone = new Random().Next(10).ToString(),
// Add preferred supplier discounts
PartnerDiscount = new Random().Next(10),
ShippingDiscount = new Random().Next(10),
VolumeDiscountPercent = new Random().Next(10),
SupplierType = ctx.SupplierTypes.FirstOrDefault(x => x.Description == "Preferred"),
};
//-- Attach new entity to parent entity, making context aware of the new object
ctx.Suppliers.Add(supplier);
//-- Save changes, inserting new row
ctx.SaveChanges();
////-- obtain new primary key value
var supplierId = supplier.SupplierID;
var queryNewSupplier = from newSupplier in ctx.Suppliers.OfType <PreferredSupplier>()
where newSupplier.SupplierID == supplierId
select
new
{
newSupplier.CompanyName,
newSupplier.ContactName,
newSupplier.City,
newSupplier.PartnerDiscount,
newSupplier.ShippingDiscount,
newSupplier.VolumeDiscountPercent
};
gvManager.DataSource = queryNewSupplier.ToList();
gvManager.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new EF360Context())
{
//Explicit Joins
//Query across Order Deatils, Orders, Customers and Products
var queryExplicit =
(from od in ctx.OrderDetails
join o in ctx.Orders on od.OrderID equals o.OrderID
join p in ctx.Products on od.ProductID equals p.ProductID
join c in ctx.Customers on o.CustomerID equals c.CustomerID
where od.OrderID == 10248
//-- Notice how we traverse navigation properties
select new
{
c.CompanyName,
o.OrderID,
o.Freight,
od.ProductID,
p.ProductName,
od.UnitPrice,
od.Quantity,
});
gvJoin.DataSource = queryExplicit.ToList();
gvJoin.DataBind();
//Inferred Joins
//Leverage associations between entities,
//exposed as navigation property, avoiding explicit joins
var queryInfer = from od in ctx.OrderDetails
where od.OrderID == 10248
select new
{
od.Order.Customer.CompanyName,
od.OrderID,
od.Order.Freight,
od.ProductID,
od.Product.ProductName,
od.UnitPrice,
od.Quantity,
};
gvInferred.DataSource = queryInfer.ToList();
gvInferred.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new EF360Context())
{
//-- (1)This time we query products table
//-- and retrieve a single record with CategoryID == 1.
//var query =
// from s in ctx.Suppliers.Include("SupplierTaxInfo.SupplierSolvent")
// where s.SupplierID == 1
// select s;
//List<string> products = new List<string>();
//gvEF.DataSource = products;
//gvEF.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
// Quantifying Operation
using (var ctx = new EF360Context())
{
// Quantifying Operation (Are there any records in the sequence?)
var anyCustomers = ctx.Customers.Any();
gvAnyCustomers.DataSource = anyCustomers.ToString(CultureInfo.InvariantCulture);
gvAnyCustomers.DataBind();
// Quantifying Operation (Are there any customers in TX?)
var anyCustomersInTX = ctx.Customers.Any(c => c.Region == "TX");
gvCustomersInTX.DataSource = anyCustomersInTX.ToString(CultureInfo.InvariantCulture);
gvCustomersInTX.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
// Keep in mind: Querying EF conceptual model, not the database
using (var ctx = new EF360Context())
{
// Retrieve data from stored procedure setup as a Function Import
// This query returns all columns from the Product table
var topTenRaw = ctx.TenMostExpensiveProducts();
// As all columns for the table are returned, use Linq query with anonymous type
// to filter for columns that are needed. This Linq query also forces enumeration.
var topTen = topTenRaw.Select(x => new { x.ProductID, x.ProductName, x.UnitPrice });
// Bind result to GridView control
gvTopTen.DataSource = topTen.ToList();
gvTopTen.DataBind();
}
}
//Custom class captures information across the object graph
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//First: Demonstrate Deferred Loading
using (var ctx = new EF360Context())
{
var queryProducts = ctx.Customers.Where(c => c.Region == "OR").ToList();
listBoxCompany.DataTextField = "CompanyName";
listBoxCompany.DataValueField = "CustomerId";
listBoxCompany.DataSource = queryProducts.ToList();
listBoxCompany.DataBind();
}
}
listBoxCompany.BackColor = Color.FromName("#FFFBD6");
}
protected void Page_Load(object sender, EventArgs e)
{
//Attaching Entities: http://msdn.microsoft.com/en-us/library/gg696174(v=vs.103).aspx
//When you execute a query in a context, the returned entities are automatically attached to the context (unless you use the AsNoTracking method to execute the query). The entities are attached in the Unchanged state. You can also attach entities that are obtained from a source other than a query and are known to already exist in the database by using the System.Data.Entity.DbSet.Attach( method.
//In the following example, the GetExistingDepartment method returns the Department entity that exists in the database. This entity could have been queried for and updated in another thread or another tier, and now needs to be attached to this context before saving changes.
//var existingDpt = GetExistingDepartment();
//using (var context = new SchoolEntities ())
//{
// context.Departments.Attach(existingDpt);
// // When setting the entry state to Modified
// // all the properties of the entity are marked as modified.
// context.Entry(existingDpt).State = EntityState.Modified;
// context.SaveChanges();
//}
// Tip:
//If the entity being attached has related entities, those entities are also attached to the context in the Unchanged state.
using (var ctx = new EF360Context())
{
var customer = new Customer
{
CustomerID = "60861",
ContactTitle = "John Smith",
CompanyName = "Microsoft"
};
ctx.Customers.Attach(customer);
ctx.Entry(customer).State = EntityState.Modified;
ctx.SaveChanges();
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new EF360Context())
{
// Build adhoc parameterized SQL statement
var sql = "select * from customers where region=@Region";
// Build strongly-typed parameter, note how we add precision
// to help minimize SQL injection attacks
var parameter = new DbParameter[]
{
new SqlParameter
{
DbType = DbType.String,
Precision = 2,
ParameterName = "Region",
Value = "OR",
}
};
// Leverage SQLQuery() method from Database property to execute.
// Result is a materialized, strongly-typed collection of customers.
var query = ctx.Database.SqlQuery <Customer>(sql, parameter);
gvQuerySyntax.DataSource = query.ToList();
gvQuerySyntax.DataBind();
// Next, leverage ExecuteSqlCommand() method to add and then delete data
sql = @"Insert into customers(CustomerId, CompanyName) values(@p0, @p1)";
// Note that the SaveChanges() command is unnessecary as we are
// directly accessing the database
var rowcount = ctx.Database.ExecuteSqlCommand(sql, "abcde", "Test Customer");
// Next, leverage ExecuteSqlCommand() method to add and then delete data
sql = @"delete from customers where CompanyName=@p0";
// Note that the SaveChanges() command is unnessecary as we are
// directly accessing the database
rowcount = ctx.Database.ExecuteSqlCommand(sql, "Test Customer");
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new EF360Context())
{
//-- Add new employee which maps to employee table
var employee = new Employee
{
//-- Add general information to Employee table
FirstName = "Bill",
LastName = "Gates" + new Random().Next(100),
PostalCode = new Random().Next(10000).ToString(),
//-- Add SSN which maps to EmployeePersonal table
SSN = new Random().Next(1000000000).ToString(),
//-- Add employee financial information which maps to EmployeeFinancial Table
CreditScore = new Random().Next(100),
CorporateRiskScore = new Random().Next(9)
};
ctx.Employees.Add(employee);
var result = ctx.SaveChanges();
//-- Get new product key
int employeeId = employee.EmployeeID;
var query = from emp in ctx.Employees
where emp.EmployeeID == employeeId
select new
{
emp.FirstName,
emp.LastName,
emp.PostalCode,
emp.CreditScore,
emp.CorporateRiskScore,
emp.SSN
};
gvEntitySplitting.DataSource = query.ToList();
gvEntitySplitting.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new EF360Context())
{
//-- StateTaxAudit entity "inherits" from TaxAudit entity.
//-- No longer need to navigate from StateTaxAudit to TaxAudit
//-- to get TaxAudit Information.
var query = from stateAudit in ctx.TaxAudits.OfType <StateTaxAudit>().OrderBy(x => x.AuditDate) select stateAudit;
gvBefore.DataSource = query.ToList();
gvBefore.DataBind();
//-- Create new StateTaxAudit record
StateTaxAudit stateTaxAudit = new StateTaxAudit();
//-- Fields from StateTaxAudit
stateTaxAudit.Agency = "Texas Franchise Tax Board";
stateTaxAudit.State = "TX";
stateTaxAudit.SalesUseTax = false;
stateTaxAudit.ID = new Random().Next(100000);
//-- Fields inherited from TaxAudit
stateTaxAudit.Audtor = "Rob Vettor";
stateTaxAudit.AuditReason = "Routine";
stateTaxAudit.Adjustment = Convert.ToDecimal(new Random().Next(10000000));
stateTaxAudit.AuditDate = DateTime.Now;
//-- Attach to parent, TaxAudit
ctx.TaxAudits.Add(stateTaxAudit);
//-- Next, EF first creates new TaxAudit AND StateTaxAudit Record
ctx.SaveChanges();
var queryAfter = from stateAudit in ctx.TaxAudits.OfType <StateTaxAudit>().OrderBy(t => t.AuditDate) select stateAudit;
gvAfter.DataSource = queryAfter.ToList();
gvAfter.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
listBoxSupplierTypes.Items.Add(new ListItem("Standard", "1"));
listBoxSupplierTypes.Items.Add(new ListItem("Preferred", "2"));
listBoxSupplierTypes.Items.Add(new ListItem("Partner", "3"));
listBoxSupplierTypes.Items.Add(new ListItem("Partner", "4"));
listBoxSupplierTypes.Items.Add(new ListItem("Invalid", "1"));
}
using (var ctx = new EF360Context())
{
var queryExplicit =
(from x in ctx.Suppliers
where x.SupplierType.ID == 1
select new
{
x.SupplierID,
x.CompanyName,
x.Address,
x.City,
x.Region,
x.PostalCode,
x.Phone,
x.Fax
}).Take(5);
//var queryExplicit = ctx.SupplierTypes.Where(x => x.ID == 1).ForEach(y =>
//{
//});
gvExplicit.DataSource = queryExplicit.ToList();
gvExplicit.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
using (var ctx = new EF360Context())
{
ctx.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
#region Ignore for Demo -- Create 'Before Result Set'
var first = ctx.Employees.FirstOrDefault();
var queryBefore =
ctx.Employees.Where(x => x.EmployeeID == first.EmployeeID)
.Select(y => new { y.EmployeeID, y.LastName, y.FirstName, y.City, y.PostalCode });
gvBefore.DataSource = queryBefore.ToList();
gvBefore.DataBind();
#endregion
// Get First Employee
var employee = ctx.Employees.FirstOrDefault();
// Peak into 'state' of customer entity,
// as tracked by the context object
var state = ctx.Entry(employee).State;
// Peak into 'initial values' of current entity,
// as cached by the Context object
var address = ctx.Entry(employee).Property(x => x.Address).OriginalValue;
var city = ctx.Entry(employee).Property(x => x.City).OriginalValue;
var zip = ctx.Entry(employee).Property(x => x.PostalCode).OriginalValue;
// Modify postal code
employee.PostalCode = (int.Parse(employee.PostalCode) + 1).ToString();
// Modifications will have changed 'State' of entity
state = ctx.Entry(employee).State;
// Peak into 'current values' of current entity,
// as cached by the Context object
address = ctx.Entry(employee).Property(x => x.Address).CurrentValue;
city = ctx.Entry(employee).Property(x => x.City).CurrentValue;
zip = ctx.Entry(employee).Property(x => x.PostalCode).CurrentValue;
// Perform Update
// Call SumbitChanges() to commit our change.
// Based upon state of the current entity, entity framework will
// build appropriate Update and update both Developer and Order tables
ctx.SaveChanges();
// Check entity state again
state = ctx.Entry(employee).State;
// Call SaveChanges again -- this time no update
ctx.SaveChanges();
// Requery Category from database
var queryAfter =
ctx.Employees.Where(x => x.EmployeeID == employee.EmployeeID)
.Select(y => new { y.EmployeeID, y.LastName, y.FirstName, y.City, y.PostalCode });
gvAfter.DataSource = queryAfter.ToList();
gvAfter.DataBind();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
using (var ctx = new EF360Context())
{
#region Ignore for Demo -- Create 'Before Result Set'
var queryBefore =
from c in ctx.Customers.Include("Orders")
where c.CustomerID == "LONEP"
select new
{
c.Address,
c.City,
c.CompanyName,
c.ContactName,
c.PostalCode,
FreightCosts = c.Orders.FirstOrDefault().Freight
};
gvInsertBefore.DataSource = queryBefore.ToList();
gvInsertBefore.DataBind();
#endregion
// Update Code
// Note the use of Include() to eager join Orders with Developer
var customer = ctx.Customers.Include(x => x.Orders).SingleOrDefault(c => c.CustomerID == "LONEP");
// Peak into 'state' of customer entity,
// as tracked by the context object
var state = ctx.Entry(customer).State;
// Peak into 'initial values' of current entity,
// as tracked by the context object
var address = ctx.Entry(customer).Property(x => x.Address).OriginalValue;
var city = ctx.Entry(customer).Property(x => x.City).OriginalValue;
var zip = ctx.Entry(customer).Property(x => x.PostalCode).OriginalValue;
// Modify postal code
customer.PostalCode = (int.Parse(customer.PostalCode) + 1).ToString();
// Modify freight charge
// Get freight charge for first order
var freight = Convert.ToInt32(customer.Orders.FirstOrDefault().Freight);
// Increase frieght charge by $1.00
customer.Orders.FirstOrDefault().Freight = freight + 1;
// Modifications will have changed 'State' of entity
state = ctx.Entry(customer).State;
// Peak into 'current values' of current entity
address = ctx.Entry(customer).Property(x => x.Address).CurrentValue;
city = ctx.Entry(customer).Property(x => x.City).CurrentValue;
zip = ctx.Entry(customer).Property(x => x.PostalCode).CurrentValue;
// Perform Update
// Call SumbitChanges() to commit our change.
// Based upon state of the current entity, entity framework will
// build appropriate Update and update both Developer and Order tables
ctx.SaveChanges();
var currentCustomerZip = customer.PostalCode;
// Check entity state again
state = ctx.Entry(customer).State;
// Call SaveChanges again -- this time no update
ctx.SaveChanges();
#region Ignore -- Create 'After Result Set'
var queryAfter =
from c in ctx.Customers
where c.CustomerID == "LONEP"
select new
{
c.Address,
c.City,
c.CompanyName,
c.ContactName,
c.PostalCode,
FreightCosts = c.Orders.FirstOrDefault().Freight
};
#endregion
gvInsertAfter.DataSource = queryAfter.ToList();
gvInsertAfter.DataBind();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new EF360Context())
{
var region = "OR";
var customerId = "GROWL";
// Add new customer, Growling Dog to customers.
var customer = new Customer {
CustomerID = "GROWL", CompanyName = "Growling Dog", Region = "OR"
};
ctx.Customers.Attach(customer);
ctx.Entry(customer).State = EntityState.Added;
ctx.SaveChanges();
// Loads Orgeon customers into DbContext instance.
// Load() forces execution of Linq query
ctx.Customers.Where(x => x.Region == region).Load();
// Get reference to DbSet local property, which is typeof IObservable
var localCustomers = ctx.Customers.Local;
// Add new customers to local DbContext instance
localCustomers.Add(new Customer {
CustomerID = "MADBR", CompanyName = "Mad Bear", Region = "OR"
});
// Remove 'Growling Dog' from local collection, which replicates to DbContext instance
// Find first searches context, then, if necessary, queries database
localCustomers.Remove(ctx.Customers.Find(customerId));
//Examine contents of context by using 'Local property' of DbContext -- no trip to DB
//Local Collection:
// Growling Dog excluded as has been marked for deletion
// Mad Bear included as has been added, but not yet saved -- key is null
var customersInContext =
localCustomers.Select(
x => new { x.CustomerID, x.CompanyName, EntityState = ctx.Entry(x).State.ToString() }).ToList();
//Examine contents of database -- queries database
// Growling Dog marked for deletion
// Mad Bear not yet added
var customersInDatabase = ctx.Customers.Where(x => x.Region == region).ToList()
.Where(x => x.Region == region)
.Select(x => new { x.CustomerID,
x.CompanyName,
EntityState = ctx.Entry(x).State.ToString() })
.ToList();
gvLocal.DataSource = customersInContext;
gvLocal.DataBind();
gvDatabase.DataSource = customersInDatabase;
gvDatabase.DataBind();
// Clean Up
localCustomers.Remove(ctx.Customers.Find("MADBR"));
ctx.SaveChanges();
// Purposely update the underlying Carrier table so that the RowVersion value changes
//ctx.Database.ExecuteSqlCommand(@"DELETE dbo.Customers WHERE CustomerID = 'GROWL'");
}
}
