public FormAddSalesOrderDetail(AdventureWorksEntities ctx, SalesOrderHeader order)
{
InitializeComponent();
parentCxt = ctx;
currentOrder = order;
}
public virtual IEnumerable<SpecialOffer_ReadListOutput> ReadList()
{
IEnumerable<SpecialOffer_ReadListOutput> res = null;
using (AdventureWorksEntities ctx = new AdventureWorksEntities())
{
var src = from obj in ctx.SpecialOffer select obj;
#region Source filter
// CUSTOM_CODE_START: add custom filter criteria to the source query for ReadList operation below
// src = src.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
var qry = from obj in src
select new SpecialOffer_ReadListOutput() {
SpecialOfferId = obj.SpecialOfferId,
Description = obj.Description,
// CUSTOM_CODE_START: set the IsActive output parameter of ReadList operation below
IsActive = (obj.StartDate == null || obj.StartDate < DateTime.Today) &&
(obj.EndDate == null || obj.EndDate > DateTime.Today), // CUSTOM_CODE_END
Category = obj.Category,
};
#region Result filter
// CUSTOM_CODE_START: add custom filter criteria to the result query for ReadList operation below
// qry = qry.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
ErrorList.Current.AbortIfHasErrors(HttpStatusCode.BadRequest);
res = qry.ToList();
}
return res;
}
public static Product GetProduct(int ProductID)
{
var db = new AdventureWorksEntities();
var data = from o in db.Products where o.ProductID == ProductID select o;
if (data.Count() == 0) throw new Exception("Cannot find Product with ID " + ProductID.ToString());
return data.FirstOrDefault();
}
static void Main(string[] args)
{
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
var query1 = AWEntities
.Contacts
.Where(c => c.LastName == "Jones")
.Select(c => new MyContact {
LastName = c.LastName
});
// Execute the first query and print the count.
query1.ToList();
Console.WriteLine("Count: " + count);
//Reset the count variable.
count = 0;
var query2 = AWEntities
.Contacts
.Where(c => c.LastName == "Jones")
.Select(c => new MyContact {
LastName = c.LastName
})
.Select(my => my.LastName);
// Execute the second query and print the count.
query2.ToList();
Console.WriteLine("Count: " + count);
}
Console.WriteLine("Hit enter...");
Console.Read();
}
public virtual IEnumerable<ShipMethod_ReadListOutput> ReadList()
{
IEnumerable<ShipMethod_ReadListOutput> res = null;
using (AdventureWorksEntities ctx = new AdventureWorksEntities())
{
var src = from obj in ctx.ShipMethod select obj;
#region Source filter
// CUSTOM_CODE_START: add custom filter criteria to the source query for ReadList operation below
// src = src.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
var qry = from obj in src
select new ShipMethod_ReadListOutput() {
ShipMethodId = obj.ShipMethodId,
Name = obj.Name,
};
#region Result filter
// CUSTOM_CODE_START: add custom filter criteria to the result query for ReadList operation below
// qry = qry.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
ErrorList.Current.AbortIfHasErrors(HttpStatusCode.BadRequest);
res = qry.ToList();
}
return res;
}
public static List <CreditCard> GetCreditCardID()
{
AdventureWorksEntities dataContext = new AdventureWorksEntities();
var result = dataContext.CreditCards;
return(result.ToList());
}
public virtual IEnumerable <SpecialOffer_ReadListOutput> ReadList()
{
IEnumerable <SpecialOffer_ReadListOutput> res = null;
using (AdventureWorksEntities ctx = new AdventureWorksEntities())
{
var src = from obj in ctx.SpecialOffer select obj;
#region Source filter
// CUSTOM_CODE_START: add custom filter criteria to the source query for ReadList operation below
// src = src.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
var qry = from obj in src
select new SpecialOffer_ReadListOutput()
{
SpecialOfferId = obj.SpecialOfferId,
Description = obj.Description,
// CUSTOM_CODE_START: set the IsActive output parameter of ReadList operation below
IsActive = (obj.StartDate == null || obj.StartDate < DateTime.Today) &&
(obj.EndDate == null || obj.EndDate > DateTime.Today), // CUSTOM_CODE_END
Category = obj.Category,
};
#region Result filter
// CUSTOM_CODE_START: add custom filter criteria to the result query for ReadList operation below
// qry = qry.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
ErrorList.Current.AbortIfHasErrors(HttpStatusCode.BadRequest);
res = qry.ToList();
}
return(res);
}
public virtual SalesOrder_CreateOutput Create(SalesOrder_CreateInput _data)
{
SalesOrder_CreateOutput res = new SalesOrder_CreateOutput();
using (AdventureWorksEntities ctx = new AdventureWorksEntities())
{
EntityState state = EntityState.Added;
SalesOrder obj = new SalesOrder();
var entry = ctx.Entry(obj);
entry.State = state;
entry.CurrentValues.SetValues(_data);
// CUSTOM_CODE_START: use the Customer input parameter of Create operation below
UpdateCustomer(ctx, obj, _data.Customer); // CUSTOM_CODE_END
// CUSTOM_CODE_START: use the Payment input parameter of Create operation below
UpdatePayment(ctx, obj, _data.Payment); // CUSTOM_CODE_END
// CUSTOM_CODE_START: use the Sales input parameter of Create operation below
UpdateSales(ctx, obj, _data.Sales); // CUSTOM_CODE_END
// CUSTOM_CODE_START: add custom code for Create operation below
obj.OrderDate = DateTime.Now;
obj.ModifiedDate = DateTime.Now;
// CUSTOM_CODE_END
ErrorList.Current.AbortIfHasErrors(HttpStatusCode.BadRequest);
ctx.SaveChanges();
ServiceUtil.CopyProperties(obj, res);
}
return res;
}
public static List <CurrencyRate> GetCurrencyRateID()
{
AdventureWorksEntities dataContext = new AdventureWorksEntities();
var result = dataContext.CurrencyRates;
return(result.ToList());
}
public static List <SalesTerritory> GetSalesTerritoryID()
{
AdventureWorksEntities dataContext = new AdventureWorksEntities();
var result = dataContext.SalesTerritories;
return(result.ToList());
}
public static List<Tuple<string, string>> GetEmployees(string filter1, string filter2)
{
using (var context = new AdventureWorksEntities())
{
var employeeSet = ((IObjectContextAdapter)context).ObjectContext.CreateObjectSet<Employee>("Employees");
Employee employee = new Employee();
ObjectQuery<Employee> employees = employeeSet.Where("1=1");
if (string.IsNullOrEmpty(filter1) == false)
{
employees = employees.Where(filter1);
}
if (string.IsNullOrEmpty(filter2) == false)
{
employees = employees.Where(filter2);
}
var list = from e in employees.ToList()
select Tuple.Create(e.Contact.FirstName, e.BirthDate.ToString());
return new List<Tuple<string, string>>(list);
}
}
public static void CallInlineFunction()
{
Console.WriteLine("Starting method 'CallInlineFunction'");
//<snippetCallInlineFunction>
// Query that calls the OrderTotal function to recalculate the order total.
string queryString = @"USING Microsoft.Samples.Entity;
FUNCTION OrderTotal(o SalesOrderHeader) AS
(o.SubTotal + o.TaxAmt + o.Freight)
SELECT [order].TotalDue AS currentTotal, OrderTotal([order]) AS calculated
FROM AdventureWorksEntities.SalesOrderHeaders AS [order]
WHERE [order].Contact.ContactID = @customer";
int customerId = 364;
using (AdventureWorksEntities context =
new AdventureWorksEntities())
{
ObjectQuery <DbDataRecord> query = new ObjectQuery <DbDataRecord>(queryString, context);
query.Parameters.Add(new ObjectParameter("customer", customerId));
foreach (DbDataRecord rec in query)
{
Console.WriteLine("Order Total: Current - {0}, Calculated - {1}.",
rec[0], rec[1]);
}
}
//</snippetCallInlineFunction>
}
//person.address deki addressid=billtoaddressıd ve addressid=shiptoadressid
public static List <Address> GetBillToAddressID()
{
AdventureWorksEntities dataContext = new AdventureWorksEntities();
var result = dataContext.Addresses;
return(result.ToList());
}
public void Update_existing_Product_with_new_Contact_with_pk_identity_by_value()
{
using (var context = new AdventureWorksEntities())
{
//Get the food category into the statemanager...
//so the subsequent query will bring span in product.Contact? Is this the expected behavior?
Contact contact = context.Contacts.Single(c => c.FirstName == "Alex");
//Find an existing product that already has a category i.e. do fixup.
SalesOrderHeader order = context.SalesOrderHeaders.Single(o => o.ModifiedDate == DateTime.Now && o.Contact.FirstName == "Alex");
//RULE: we should have done fixup by now...
//Debug.ReferenceEquals(order.Contact, food);
Contact misc = new Contact {
FirstName = "Bob"
};
order.ContactID = misc.ContactID;
//NOTE: without this line Misc is not added, and we would be attempting to relate to something in
// the database
context.Contacts.AddObject(misc);
context.SaveChanges();
//Debug.Assert(misc.ID == product.ContactID);
//Debug.ReferenceEquals(misc, product.Contact);
}
}
public List <Vendor> FullVendorList()
{
AdventureWorksEntities entities = new AdventureWorksEntities();
var products = from v in entities.Vendors select v;
return(products.ToList <Vendor>());
}
public void Create_new_Product_and_new_Contacts_with_pk_identity_and_relate_by_value()
{
using (var context = new AdventureWorksEntities())
{
//c.ID is store generated
Contact c1 = new Contact
{
FirstName = "Alex"
};
Contact c2 = new Contact
{
FirstName = "Bob"
};
SalesOrderHeader order = new SalesOrderHeader
{
SalesOrderID = 1,
ModifiedDate = DateTime.Now
};
context.SalesOrderHeaders.AddObject(order);
context.Contacts.AddObject(c1);
context.Contacts.AddObject(c2);
// Throw on SaveChanges() FK points to more than one Entity by temp key
context.SaveChanges();
}
}
static private void ReturnAnonymousTypeWithObjectQuery()
{
//<snippetReturnAnonymousTypeWithObjectQuery>
using (AdventureWorksEntities advWorksContext =
new AdventureWorksEntities())
{
string myQuery = @"SELECT p.ProductID, p.Name FROM
AdventureWorksEntities.Product as p";
try
{
foreach (DbDataRecord rec in
new ObjectQuery <DbDataRecord>(myQuery, advWorksContext))
{
Console.WriteLine("ID {0}; Name {1}", rec[0], rec[1]);
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.ToString());
}
}
//</snippetReturnAnonymousTypeWithObjectQuery>
}
static private void AggregateDataWithObjectQuery()
{
//<snippetAggregateDataWithObjectQuery>
using (AdventureWorksEntities advWorksContext =
new AdventureWorksEntities())
{
string esqlQuery = @"SELECT contactID, AVG(order.TotalDue)
FROM AdventureWorksEntities.SalesOrderHeader
AS order GROUP BY order.Contact.ContactID as contactID";
try
{
foreach (DbDataRecord rec in
new ObjectQuery <DbDataRecord>(esqlQuery, advWorksContext))
{
Console.WriteLine("ContactID = {0} Average TotalDue = {1} ",
rec[0], rec[1]);
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.ToString());
}
}
//</snippetAggregateDataWithObjectQuery>
}
public static void GroupByPartition()
{
Console.WriteLine("Starting method 'GroupPartition'");
//<snippetGroupByPartition>
using (AdventureWorksEntities context =
new AdventureWorksEntities())
{
// Query that returns average TotalDue for a contact.
string queryString = @"SELECT TOP(@number1) contactID, AVG(GroupPartition(order.TotalDue))
FROM AdventureWorksEntities.SalesOrderHeaders
AS order GROUP BY order.Contact.ContactID as contactID";
ObjectQuery <DbDataRecord> query1 = new ObjectQuery <DbDataRecord>(queryString, context);
query1.Parameters.Add(new ObjectParameter("number1", 10));
foreach (DbDataRecord rec in query1)
{
Console.WriteLine("ContactID = {0} Average TotalDue = {1} ",
rec[0], rec[1]);
}
queryString = @"SELECT TOP(@number2) contactID, AVG(order.TotalDue)
FROM AdventureWorksEntities.SalesOrderHeaders
AS order GROUP BY order.Contact.ContactID as contactID";
ObjectQuery <DbDataRecord> query2 = new ObjectQuery <DbDataRecord>(queryString, context);
query2.Parameters.Add(new ObjectParameter("number2", 10));
foreach (DbDataRecord rec in query2)
{
Console.WriteLine("ContactID = {0} Average TotalDue = {1} ",
rec[0], rec[1]);
}
}
//</snippetGroupByPartition>
}
public ActionResult Index()
{
ViewBag.FileMessage = "";
ViewBag.ExceptionMessage = "";
ViewBag.Message = "";
var msg1 = "";
var msg2 = "";
var vm = new FilesViewModel();
try
{
var cs = ConfigurationManager.AppSettings["StorageConnectionString"];
var account = CloudStorageAccount.Parse(cs);
var fileClient = account.CreateCloudFileClient();
var share = fileClient.GetShareReference("workspace-file-storage");
if (share.Exists())
{
var rootDir = share.GetRootDirectoryReference();
if (rootDir.Exists())
{
var contents = rootDir.ListFilesAndDirectories();
vm.ViaAPI = contents.Where(i => i.GetType() == typeof(CloudFile)).Select(f => new FileViewModel()
{
Name = Path.GetFileName(f.Uri.LocalPath)
}).ToArray();
msg1 = $"Got {vm.ViaAPI.Length} files via API";
}
}
}
catch (Exception ex)
{
msg1 = ex.Message;
}
try
{
var files = Directory.GetFiles("c:\\server\\workspace\\client\\files");
vm.ViaSymlink = files.Select(f => new FileViewModel()
{
Name = WebUtility.UrlEncode(new FileInfo(f).Name)
}).ToArray();
msg2 = $"Got {vm.ViaSymlink.Length} files via symlink";
}
catch (Exception ex)
{
msg2 = ex.Message;
}
ViewBag.Message1 = msg1;
ViewBag.Message2 = msg2;
var ef = new AdventureWorksEntities();
var q = ef.Products.Take(10);
vm.Products = q.ToArray();
return(View(vm));
}
protected void UpdateSales(AdventureWorksEntities ctx, SalesOrder obj, SalesInfo _data)
{
obj.TerritoryIdObject = ctx.SalesTerritory.Find(_data.TerritoryId);
if (_data.TerritoryId != null && obj.TerritoryIdObject == null)
{
ErrorList.Current.AddError("Cannot find Sales Territory with ID {0}.", _data.TerritoryId);
}
obj.SalesPersonIdObject = ctx.SalesPerson.Find(_data.SalesPersonId);
if (_data.SalesPersonId != null && obj.SalesPersonIdObject == null)
{
ErrorList.Current.AddError("Cannot find Sales Person with ID {0}.", _data.SalesPersonId);
}
// remove sales reason that are not in the provided list
obj.ReasonObjectList.Where(r => _data.SalesReason == null || !_data.SalesReason.Contains(r.SalesReasonId))
.ToList().ForEach(r => obj.ReasonObjectList.Remove(r));
if (_data.SalesReason != null)
{
// add sales reasons from provided list that don't exist yet
_data.SalesReason.Where(rId => obj.ReasonObjectList.Where(r => r.SalesReasonId == rId).ToList().Count == 0)
.ToList().ForEach(rId => obj.ReasonObjectList.Add(new SalesOrderReason()
{
SalesOrderObject = obj,
SalesReasonId = rId,
ModifiedDate = DateTime.Now
}));
}
}
public async Task GetData()
{
try
{
this.IsBusy = true;
this.IsBrowsing = true;
this.connection = new AdventureWorksEntities(new Uri(this.url));
var query = await Task.Factory.FromAsync(
this.connection.Customers.BeginExecute(null, null),
(result) => this.connection.Customers.EndExecute(result));
this.customers = query.ToList();
this.currentCustomer = 0;
this.OnPropertyChanged("Current");
this.IsAtStart = true;
this.IsAtEnd = (this.customers.Count == 0);
this.LastError = String.Empty;
}
catch (DataServiceQueryException dsqe)
{
this.LastError = dsqe.Message;
}
finally
{
this.IsBusy = false;
}
}
public virtual IEnumerable <Product_ReadListOutput> ReadList()
{
IEnumerable <Product_ReadListOutput> res = null;
using (AdventureWorksEntities ctx = new AdventureWorksEntities())
{
var src = from obj in ctx.Product select obj;
#region Source filter
// CUSTOM_CODE_START: add custom filter criteria to the source query for ReadList operation below
// src = src.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
var qry = from obj in src
select new Product_ReadListOutput()
{
ProductId = obj.ProductId,
Name = obj.Name,
// CUSTOM_CODE_START: set the IsActive output parameter of ReadList operation below
IsActive = (obj.SellEndDate == null || obj.SellEndDate > DateTime.Today) &&
obj.DiscontinuedDate == null, // CUSTOM_CODE_END
ProductSubcategoryId = obj.ProductSubcategoryIdObject.ProductSubcategoryId,
ProductModelId = obj.ProductModelIdObject.ProductModelId,
};
#region Result filter
// CUSTOM_CODE_START: add custom filter criteria to the result query for ReadList operation below
// qry = qry.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
ErrorList.Current.AbortIfHasErrors(HttpStatusCode.BadRequest);
res = qry.ToList();
}
return(res);
}
public async Task GetData()
{
try
{
this.IsBusy = true;
// await Task.Delay(5000);
this.connection = new AdventureWorksEntities(new Uri(this.url));
var query = await Task.Factory.FromAsync(
this.connection.Customers.BeginExecute(null, null),
(result) => this.connection.Customers.EndExecute(result));
this.customers = query.ToList();
this.currentCustomer = 0;
this.OnPropertyChanged("Current");
this.IsAtStart = true;
this.IsAtEnd = (this.customers.Count == 0);
}
catch (DataServiceQueryException dsqe)
{
// TODO: Handle errors
}
finally
{
this.IsBusy = false;
}
}
static public void ESQLPagingWithObjectQuery()
{
//<snippetESQLPagingWithObjectQuery>
using (AdventureWorksEntities context =
new AdventureWorksEntities())
{
// Create a query that takes two parameters.
string queryString =
@"SELECT VALUE product FROM
AdventureWorksEntities.Products AS product
order by product.ListPrice SKIP @skip LIMIT @limit";
ObjectQuery <Product> productQuery =
new ObjectQuery <Product>(queryString, context);
// Add parameters to the collection.
productQuery.Parameters.Add(new ObjectParameter("skip", 3));
productQuery.Parameters.Add(new ObjectParameter("limit", 5));
// Iterate through the collection of Contact items.
foreach (Product result in productQuery)
{
Console.WriteLine("ID: {0}; Name: {1}",
result.ProductID, result.Name);
}
}
//</snippetESQLPagingWithObjectQuery>
}
public virtual IEnumerable<Product_ReadListOutput> ReadList()
{
IEnumerable<Product_ReadListOutput> res = null;
using (AdventureWorksEntities ctx = new AdventureWorksEntities())
{
var src = from obj in ctx.Product select obj;
#region Source filter
// CUSTOM_CODE_START: add custom filter criteria to the source query for ReadList operation below
// src = src.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
var qry = from obj in src
select new Product_ReadListOutput() {
ProductId = obj.ProductId,
Name = obj.Name,
// CUSTOM_CODE_START: set the IsActive output parameter of ReadList operation below
IsActive = (obj.SellEndDate == null || obj.SellEndDate > DateTime.Today)
&& obj.DiscontinuedDate == null, // CUSTOM_CODE_END
ProductSubcategoryId = obj.ProductSubcategoryIdObject.ProductSubcategoryId,
ProductModelId = obj.ProductModelIdObject.ProductModelId,
};
#region Result filter
// CUSTOM_CODE_START: add custom filter criteria to the result query for ReadList operation below
// qry = qry.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
ErrorList.Current.AbortIfHasErrors(HttpStatusCode.BadRequest);
res = qry.ToList();
}
return res;
}
static private void OrderTwoUnionizedQueriesWithObjectQuery()
{
//<snippetOrderTwoUnionizedQueriesWithObjectQuery>
using (AdventureWorksEntities advWorksContext =
new AdventureWorksEntities())
{
String esqlQuery = @"SELECT P2.Name, P2.ListPrice
FROM ((SELECT P1.Name, P1.ProductID as Pid, P1.ListPrice
FROM AdventureWorksEntities.Product as P1
where P1.Name like 'A%')
union all
(SELECT P1.Name, P1.ProductID as Pid, P1.ListPrice
FROM AdventureWorksEntities.Product as P1
WHERE P1.Name like 'B%')
) as P2
ORDER BY P2.Name";
try
{
foreach (DbDataRecord rec in
new ObjectQuery <DbDataRecord>(esqlQuery, advWorksContext))
{
Console.WriteLine("Name: {0}; ListPrice: {1}", rec[0], rec[1]);
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.ToString());
}
}
//</snippetOrderTwoUnionizedQueriesWithObjectQuery>
}
static public void ParameterizedQueryWithObjectQuery()
{
//<snippetParameterizedQueryWithObjectQuery>
using (AdventureWorksEntities context =
new AdventureWorksEntities())
{
// Create a query that takes two parameters.
string queryString =
@"SELECT VALUE Contact FROM AdventureWorksEntities.Contacts
AS Contact WHERE Contact.LastName = @ln AND
Contact.FirstName = @fn";
ObjectQuery <Contact> contactQuery =
new ObjectQuery <Contact>(queryString, context);
// Add parameters to the collection.
contactQuery.Parameters.Add(new ObjectParameter("ln", "Adams"));
contactQuery.Parameters.Add(new ObjectParameter("fn", "Frances"));
// Iterate through the collection of Contact items.
foreach (Contact result in contactQuery)
{
Console.WriteLine("Last Name: {0}; First Name: {1}",
result.LastName, result.FirstName);
}
}
//</snippetParameterizedQueryWithObjectQuery>
}
static public void OrderTwoUnionizedQueriesWithObjectQuery()
{
//<snippetOrderTwoUnionizedQueriesWithObjectQuery>
using (AdventureWorksEntities context =
new AdventureWorksEntities())
{
String esqlQuery = @"SELECT P2.Name, P2.ListPrice
FROM ((SELECT P1.Name, P1.ProductID as Pid, P1.ListPrice
FROM AdventureWorksEntities.Products as P1
where P1.Name like 'A%')
union all
(SELECT P1.Name, P1.ProductID as Pid, P1.ListPrice
FROM AdventureWorksEntities.Products as P1
WHERE P1.Name like 'B%')
) as P2
ORDER BY P2.Name";
foreach (DbDataRecord rec in
new ObjectQuery <DbDataRecord>(esqlQuery, context))
{
Console.WriteLine("Name: {0}; ListPrice: {1}", rec[0], rec[1]);
}
}
//</snippetOrderTwoUnionizedQueriesWithObjectQuery>
}
public static List <ShipMethod> GetShipMethodID()
{
AdventureWorksEntities dataContext = new AdventureWorksEntities();
var result = dataContext.ShipMethods;
return(result.ToList());
}
static public void GroupDataWithObjectQuery()
{
//<snippetGroupDataWithObjectQuery>
using (AdventureWorksEntities context =
new AdventureWorksEntities())
{
string esqlQuery = @"SELECT ln,
(SELECT c1.LastName FROM AdventureWorksEntities.Contacts
AS c1 WHERE SUBSTRING(c1.LastName ,1,1) = ln)
AS CONTACT
FROM AdventureWorksEntities.Contacts AS c2 GROUP BY SUBSTRING(c2.LastName ,1,1) AS ln
ORDER BY ln";
foreach (DbDataRecord rec in
new ObjectQuery <DbDataRecord>(esqlQuery, context))
{
Console.WriteLine("Last names that start with the letter '{0}':",
rec[0]);
List <DbDataRecord> list = rec[1] as List <DbDataRecord>;
foreach (DbDataRecord nestedRec in list)
{
for (int i = 0; i < nestedRec.FieldCount; i++)
{
Console.WriteLine(" {0} ", nestedRec[i]);
}
}
}
}
//</snippetGroupDataWithObjectQuery>
}
public virtual IEnumerable <SalesReason_ReadListOutput> ReadList()
{
IEnumerable <SalesReason_ReadListOutput> res = null;
using (AdventureWorksEntities ctx = new AdventureWorksEntities())
{
var src = from obj in ctx.SalesReason select obj;
#region Source filter
// CUSTOM_CODE_START: add custom filter criteria to the source query for ReadList operation below
// src = src.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
var qry = from obj in src
select new SalesReason_ReadListOutput()
{
SalesReasonId = obj.SalesReasonId,
Name = obj.Name,
};
#region Result filter
// CUSTOM_CODE_START: add custom filter criteria to the result query for ReadList operation below
// qry = qry.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
ErrorList.Current.AbortIfHasErrors(HttpStatusCode.BadRequest);
res = qry.ToList();
}
return(res);
}
static public void NavRelationshipWithNavProperties()
{
//<snippetNavRelationshipWithNavProperties>
using (AdventureWorksEntities context =
new AdventureWorksEntities())
{
string esqlQuery = @"SELECT c.FirstName, c.SalesOrderHeaders
FROM AdventureWorksEntities.Contacts AS c where c.LastName = @ln";
ObjectQuery <DbDataRecord> query = new ObjectQuery <DbDataRecord>(esqlQuery, context);
query.Parameters.Add(new ObjectParameter("ln", "Zhou"));
foreach (DbDataRecord rec in query)
{
// Display contact's first name.
Console.WriteLine("First Name {0}: ", rec[0]);
List <SalesOrderHeader> list = rec[1] as List <SalesOrderHeader>;
// Display SalesOrderHeader information
// associated with the contact.
foreach (SalesOrderHeader soh in list)
{
Console.WriteLine(" Order ID: {0}, Order date: {1}, Total Due: {2}",
soh.SalesOrderID, soh.OrderDate, soh.TotalDue);
}
}
}
//</snippetNavRelationshipWithNavProperties>
}
static private void ReturnEntityTypeWithObectQuery()
{
//<snippetReturnEntityTypeWithObectQuery>
using (AdventureWorksEntities advWorksContext =
new AdventureWorksEntities())
{
try
{
string queryString =
@"SELECT VALUE Product FROM AdventureWorksEntities.Product AS Product";
ObjectQuery <Product> productQuery =
new ObjectQuery <Product>(queryString, advWorksContext, MergeOption.NoTracking);
// Iterate through the collection of Product items.
foreach (Product result in productQuery)
{
Console.WriteLine("Product Name: {0}; Product ID: {1}",
result.Name, result.ProductID);
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.ToString());
}
}
//</snippetReturnEntityTypeWithObectQuery>
}
public virtual IEnumerable<SalesPerson_ReadListOutput> ReadList()
{
IEnumerable<SalesPerson_ReadListOutput> res = null;
using (AdventureWorksEntities ctx = new AdventureWorksEntities())
{
var src = from obj in ctx.SalesPerson select obj;
#region Source filter
// CUSTOM_CODE_START: add custom filter criteria to the source query for ReadList operation below
// src = src.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
var qry = from obj in src
select new SalesPerson_ReadListOutput() {
BusinessEntityId = obj.BusinessEntityId,
// CUSTOM_CODE_START: set the Name output parameter of ReadList operation below
Name = obj.BusinessEntityIdObject.BusinessEntityIdObject.LastName + ", "
+ obj.BusinessEntityIdObject.BusinessEntityIdObject.FirstName, // CUSTOM_CODE_END
// CUSTOM_CODE_START: set the IsCurrent output parameter of ReadList operation below
IsCurrent = obj.BusinessEntityIdObject.CurrentFlag, // CUSTOM_CODE_END
TerritoryId = obj.TerritoryIdObject.TerritoryId,
};
#region Result filter
// CUSTOM_CODE_START: add custom filter criteria to the result query for ReadList operation below
// qry = qry.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
ErrorList.Current.AbortIfHasErrors(HttpStatusCode.BadRequest);
res = qry.ToList();
}
return res;
}
static private void ReturnPrimitiveTypeWithObjectQuery()
{
//<snippetReturnPrimitiveTypeWithObjectQuery>
using (AdventureWorksEntities advWorksContext =
new AdventureWorksEntities())
{
string queryString = @"SELECT VALUE Length(p.Name)FROM
AdventureWorksEntities.Product AS p";
try
{
ObjectQuery <Int32> productQuery =
new ObjectQuery <Int32>(queryString, advWorksContext, MergeOption.NoTracking);
foreach (Int32 result in productQuery)
{
Console.WriteLine("{0}", result);
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.ToString());
}
}
//</snippetReturnPrimitiveTypeWithObjectQuery>
}
public void Create_new_Product_and_new_Contact_with_pk_identity_and_relate_by_value()
{
using (var context = new AdventureWorksEntities())
{
//c.ID is store generated
Contact c = new Contact
{
ContactID = 0,
FirstName = "Alex"
};
SalesOrderHeader order = new SalesOrderHeader
{
SalesOrderID = 1,
ModifiedDate = DateTime.Now,
ContactID = 0
};
// The fixup occurs
context.SalesOrderHeaders.AddObject(order);
context.Contacts.AddObject(c);
// Must not fail because of a dependency issue...
// i.e. Update must re-order so that the Contact is created before the SalesOrderHeader
context.SaveChanges();
}
}
//
// GET: /Product/
// [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope")]
public ActionResult Details(int id)
{
// using(var entities = new AdventureWorksEntities()) {
var entities = new AdventureWorksEntities();
var product = from p in entities.Products where p.ProductID == id select p;
return View(product.SingleOrDefault());
// }
}
public static List<Product> GetProductsByCategory(int CategoryID)
{
var db = new AdventureWorksEntities();
var data = from o in db.Products where o.ProductCategory.ProductCategoryID == CategoryID orderby o.Name select o;
return data.ToList();
}
private static void TestQuerySQLString()
{
string query = //@"SELECT [Extent1].[AccountNumber] AS [AccountNumber] FROM [Sales].[Customer] AS [Extent1] WHERE [Extent1].[AccountNumber] like '%3%'";// WHERE Customers.UserName = @username AND Customers.Password = @password";
@"SELECT [Extent1].[EmployeeID] AS [EmployeeID] FROM [HumanResources].[Employee] AS [Extent1] WHERE [Extent1].[Gender] like '%M%'";// WHERE Customers.UserName = @username AND Customers.Password = @password";
using (var context = new AdventureWorksEntities())
{
IEnumerable<int> results = context.Database.SqlQuery<int>(query);
var lis = results.ToList();
}
}
// public virtual void Dispose(bool disposing)
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (entities != null)
{
entities.Dispose();
entities = null;
}
}
}
private static void TestLinqToEntities()
{
using (var context = new AdventureWorksEntities())
{
var customers = from e in context.Employees.Where(p => p.Contact.FirstName.Contains(""))
from c in context.Contacts
where e.ContactID == c.ContactID
select new { DateOfBirth = e.BirthDate, Name = c.FirstName};
var lis = customers.ToList();
}
}
public virtual IEnumerable<BusinessEntityAddress_ReadListOutput> ReadList(int _businessEntityId)
{
IEnumerable<BusinessEntityAddress_ReadListOutput> res = null;
using (AdventureWorksEntities ctx = new AdventureWorksEntities())
{
var src = from obj in ctx.BusinessEntityAddress select obj;
#region Source filter
if (true)
{
// CUSTOM_CODE_START: add code for BusinessEntityId criteria of ReadList operation below
src = src.Where(o => o.BusinessEntityId == _businessEntityId); // CUSTOM_CODE_END
}
// CUSTOM_CODE_START: add custom filter criteria to the source query for ReadList operation below
// src = src.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
var qry = from obj in src
select new BusinessEntityAddress_ReadListOutput() {
// CUSTOM_CODE_START: set the AddressId output parameter of ReadList operation below
AddressId = obj.AddressId, // CUSTOM_CODE_END
// CUSTOM_CODE_START: set the AddressType output parameter of ReadList operation below
AddressType = obj.AddressTypeIdObject.Name, // CUSTOM_CODE_END
// CUSTOM_CODE_START: set the AddressLine1 output parameter of ReadList operation below
AddressLine1 = obj.AddressIdObject.AddressLine1, // CUSTOM_CODE_END
// CUSTOM_CODE_START: set the AddressLine2 output parameter of ReadList operation below
AddressLine2 = obj.AddressIdObject.AddressLine2, // CUSTOM_CODE_END
// CUSTOM_CODE_START: set the City output parameter of ReadList operation below
City = obj.AddressIdObject.City, // CUSTOM_CODE_END
// CUSTOM_CODE_START: set the State output parameter of ReadList operation below
State = obj.AddressIdObject.StateProvinceIdObject.StateProvinceCode, // CUSTOM_CODE_END
// CUSTOM_CODE_START: set the PostalCode output parameter of ReadList operation below
PostalCode = obj.AddressIdObject.PostalCode, // CUSTOM_CODE_END
// CUSTOM_CODE_START: set the Country output parameter of ReadList operation below
Country = obj.AddressIdObject.StateProvinceIdObject
.CountryRegionCodeObject.CountryRegionCode, // CUSTOM_CODE_END
};
#region Result filter
if (true)
{
}
// CUSTOM_CODE_START: add custom filter criteria to the result query for ReadList operation below
// qry = qry.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
ErrorList.Current.AbortIfHasErrors(HttpStatusCode.BadRequest);
res = qry.ToList();
}
return res;
}
public virtual IEnumerable<PersonCreditCard_ReadListOutput> ReadList(int _businessEntityId)
{
IEnumerable<PersonCreditCard_ReadListOutput> res = null;
using (AdventureWorksEntities ctx = new AdventureWorksEntities())
{
var src = from obj in ctx.PersonCreditCard select obj;
#region Source filter
if (true)
{
// CUSTOM_CODE_START: add code for BusinessEntityId criteria of ReadList operation below
src = src.Where(o => o.BusinessEntityId == _businessEntityId); // CUSTOM_CODE_END
}
// CUSTOM_CODE_START: add custom filter criteria to the source query for ReadList operation below
// src = src.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
var qry = from obj in src
select new PersonCreditCard_ReadListOutput() {
// CUSTOM_CODE_START: set the CreditCardId output parameter of ReadList operation below
CreditCardId = obj.CreditCardId, // CUSTOM_CODE_END
// CUSTOM_CODE_START: set the CreditCardName output parameter of ReadList operation below
CreditCardName = obj.CreditCardIdObject.CardType + "-*" +
obj.CreditCardIdObject.CardNumber.Substring(
obj.CreditCardIdObject.CardNumber.Length - 4), // CUSTOM_CODE_END
// CUSTOM_CODE_START: set the PersonName output parameter of ReadList operation below
PersonName = obj.BusinessEntityIdObject.LastName + ", " +
obj.BusinessEntityIdObject.FirstName, // CUSTOM_CODE_END
// CUSTOM_CODE_START: set the CardType output parameter of ReadList operation below
CardType = obj.CreditCardIdObject.CardType, // CUSTOM_CODE_END
// CUSTOM_CODE_START: set the CardNumber output parameter of ReadList operation below
CardNumber = obj.CreditCardIdObject.CardNumber, // CUSTOM_CODE_END
// CUSTOM_CODE_START: set the ExpMonth output parameter of ReadList operation below
ExpMonth = obj.CreditCardIdObject.ExpMonth, // CUSTOM_CODE_END
// CUSTOM_CODE_START: set the ExpYear output parameter of ReadList operation below
ExpYear = obj.CreditCardIdObject.ExpYear, // CUSTOM_CODE_END
};
#region Result filter
if (true)
{
}
// CUSTOM_CODE_START: add custom filter criteria to the result query for ReadList operation below
// qry = qry.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
ErrorList.Current.AbortIfHasErrors(HttpStatusCode.BadRequest);
res = qry.ToList();
}
return res;
}
private static void TestQueryBuilder()
{
using (var context = new AdventureWorksEntities())
{
var customerSet = ((IObjectContextAdapter)context).ObjectContext.CreateObjectSet<Employee>("Employees");
//ObjectQuery<SaleAd> adQuery = ((IObjectContextAdapter) context).ObjectContext.CreateQuery<SaleAd>(queryString, paramList) );
var customers = customerSet
.Where("it.Gender = @gender", new ObjectParameter("gender", "F"))
.ContainsKeywords("A", "i")
.OrderBy("it.Contact.FirstName");
var lis = customers.ToList();
}
}
public virtual void Delete(int _salesOrderId)
{
using (AdventureWorksEntities ctx = new AdventureWorksEntities())
{
EntityState state = EntityState.Deleted;
SalesOrder obj = ctx.SalesOrder.Find(_salesOrderId);
if (obj == null)
{
ErrorList.Current.CriticalError(HttpStatusCode.NotFound, "SalesOrder with id {0} not found", _salesOrderId);
}
var entry = ctx.Entry(obj);
entry.State = state;
// CUSTOM_CODE_START: add custom code for Delete operation below
// CUSTOM_CODE_END
ErrorList.Current.AbortIfHasErrors(HttpStatusCode.BadRequest);
ctx.SaveChanges();
}
}
protected void UpdateCustomer(AdventureWorksEntities ctx, SalesOrder obj, CustomerUpdate _data)
{
obj.CustomerIdObject = ctx.Customer.Find(_data.CustomerId);
if (obj.CustomerIdObject == null)
ErrorList.Current.AddError("Cannot find Customer with ID {0}.", _data.CustomerId);
if (_data.BillingAddress != null)
{
obj.BillToAddressIdObject = ctx.Address.Find(_data.BillingAddress.AddressId);
if (obj.BillToAddressIdObject == null)
ErrorList.Current.AddError("Cannot find Address with ID {0}.", _data.BillingAddress.AddressId);
}
if (_data.ShippingAddress != null)
{
obj.ShipToAddressIdObject = ctx.Address.Find(_data.ShippingAddress.AddressId);
if (obj.ShipToAddressIdObject == null)
ErrorList.Current.AddError("Cannot find Address with ID {0}.", _data.ShippingAddress.AddressId);
}
}
public static List<ProductCategory> GetCategories()
{
var db = new AdventureWorksEntities();
var data = from o in db.ProductCategories orderby o.Name select o;
return data.ToList();
}
public virtual SalesOrder_ReadOutput Read(int _salesOrderId)
{
SalesOrder_ReadOutput res = new SalesOrder_ReadOutput();
using (AdventureWorksEntities ctx = new AdventureWorksEntities())
{
SalesOrder obj = ctx.SalesOrder.Find(_salesOrderId);
if (obj == null)
{
ErrorList.Current.CriticalError(HttpStatusCode.NotFound, "SalesOrder with id {0} not found", _salesOrderId);
}
ServiceUtil.CopyProperties(obj, res);
// CUSTOM_CODE_START: populate the Customer output structure of Read operation below
res.Customer = GetCustomerInfo(obj); // CUSTOM_CODE_END
// CUSTOM_CODE_START: populate the Payment output structure of Read operation below
res.Payment = GetPaymentInfo(obj); // CUSTOM_CODE_END
// CUSTOM_CODE_START: populate the Sales output structure of Read operation below
res.Sales = GetSalesInfo(obj); // CUSTOM_CODE_END
// CUSTOM_CODE_START: add custom code for Read operation below
// CUSTOM_CODE_END
}
return res;
}
public virtual IEnumerable<SalesOrder_ReadListOutput> ReadList(SalesOrder_ReadListInput_Criteria _criteria)
{
IEnumerable<SalesOrder_ReadListOutput> res = null;
using (AdventureWorksEntities ctx = new AdventureWorksEntities())
{
var src = from obj in ctx.SalesOrder select obj;
#region Source filter
if (_criteria != null)
{
// CUSTOM_CODE_START: add code for GlobalRegion criteria of ReadList operation below
if (_criteria.GlobalRegion != null)
{
src = src.Where(o => _criteria.GlobalRegion == o.TerritoryIdObject.Group);
} // CUSTOM_CODE_END
}
// CUSTOM_CODE_START: add custom filter criteria to the source query for ReadList operation below
// src = src.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
var qry = from obj in src
select new SalesOrder_ReadListOutput() {
SalesOrderId = obj.SalesOrderId,
SalesOrderNumber = obj.SalesOrderNumber,
Status = obj.Status,
OrderDate = obj.OrderDate,
ShipDate = obj.ShipDate,
DueDate = obj.DueDate,
TotalDue = obj.TotalDue,
OnlineOrderFlag = obj.OnlineOrderFlag,
// CUSTOM_CODE_START: set the CustomerStore output parameter of ReadList operation below
CustomerStore = obj.CustomerIdObject.StoreIdObject.Name, // CUSTOM_CODE_END
// CUSTOM_CODE_START: set the CustomerName output parameter of ReadList operation below
CustomerName = obj.CustomerIdObject.PersonIdObject.LastName + ", " +
obj.CustomerIdObject.PersonIdObject.FirstName, // CUSTOM_CODE_END
SalesPersonId = obj.SalesPersonIdObject.BusinessEntityId,
TerritoryId = obj.TerritoryIdObject.TerritoryId,
};
#region Result filter
if (_criteria != null)
{
#region SalesOrderNumber
if (_criteria.SalesOrderNumberOperator != null)
{
switch (_criteria.SalesOrderNumberOperator)
{
case Operators.IsEqualTo:
qry = qry.Where(o => o.SalesOrderNumber == _criteria.SalesOrderNumber); break;
case Operators.IsNotEqualTo:
qry = qry.Where(o => o.SalesOrderNumber != _criteria.SalesOrderNumber); break;
case Operators.Contains:
qry = qry.Where(o => o.SalesOrderNumber.Contains(_criteria.SalesOrderNumber)); break;
case Operators.DoesNotContain:
qry = qry.Where(o => !o.SalesOrderNumber.Contains(_criteria.SalesOrderNumber)); break;
default:
ErrorList.Current.AddError("Unsupported operator {0} for the Sales Order Number.", _criteria.SalesOrderNumberOperator); break;
}
}
#endregion
#region Status
if (_criteria.StatusOperator != null)
{
switch (_criteria.StatusOperator)
{
case Operators.IsEqualTo:
qry = qry.Where(o => o.Status == _criteria.Status); break;
case Operators.IsNotEqualTo:
qry = qry.Where(o => o.Status != _criteria.Status); break;
default:
ErrorList.Current.AddError("Unsupported operator {0} for the Status.", _criteria.StatusOperator); break;
}
}
#endregion
#region OrderDate
if (_criteria.OrderDateOperator != null)
{
switch (_criteria.OrderDateOperator)
{
case Operators.IsEqualTo:
qry = qry.Where(o => o.OrderDate == _criteria.OrderDate); break;
case Operators.IsNotEqualTo:
qry = qry.Where(o => o.OrderDate != _criteria.OrderDate); break;
case Operators.IsEarlierThan:
qry = qry.Where(o => o.OrderDate < _criteria.OrderDate); break;
case Operators.IsLaterThan:
qry = qry.Where(o => o.OrderDate > _criteria.OrderDate); break;
case Operators.IsBetween:
qry = qry.Where(o => o.OrderDate >= _criteria.OrderDate && o.OrderDate <= _criteria.OrderDate2); break;
default:
ErrorList.Current.AddError("Unsupported operator {0} for the Order Date.", _criteria.OrderDateOperator); break;
}
}
#endregion
#region DueDate
if (_criteria.DueDateOperator != null)
{
switch (_criteria.DueDateOperator)
{
case Operators.IsEqualTo:
qry = qry.Where(o => o.DueDate == _criteria.DueDate); break;
case Operators.IsNotEqualTo:
qry = qry.Where(o => o.DueDate != _criteria.DueDate); break;
case Operators.IsEarlierThan:
qry = qry.Where(o => o.DueDate < _criteria.DueDate); break;
case Operators.IsLaterThan:
qry = qry.Where(o => o.DueDate > _criteria.DueDate); break;
case Operators.IsBetween:
qry = qry.Where(o => o.DueDate >= _criteria.DueDate && o.DueDate <= _criteria.DueDate2); break;
default:
ErrorList.Current.AddError("Unsupported operator {0} for the Due Date.", _criteria.DueDateOperator); break;
}
}
#endregion
#region TotalDue
if (_criteria.TotalDueOperator != null)
{
switch (_criteria.TotalDueOperator)
{
case Operators.IsEqualTo:
qry = qry.Where(o => o.TotalDue == _criteria.TotalDue); break;
case Operators.IsNotEqualTo:
qry = qry.Where(o => o.TotalDue != _criteria.TotalDue); break;
case Operators.IsLessThan:
qry = qry.Where(o => o.TotalDue < _criteria.TotalDue); break;
case Operators.IsNotLessThan:
qry = qry.Where(o => o.TotalDue >= _criteria.TotalDue); break;
case Operators.IsGreaterThan:
qry = qry.Where(o => o.TotalDue > _criteria.TotalDue); break;
case Operators.IsNotGreaterThan:
qry = qry.Where(o => o.TotalDue <= _criteria.TotalDue); break;
case Operators.IsBetween:
qry = qry.Where(o => o.TotalDue >= _criteria.TotalDue && o.TotalDue <= _criteria.TotalDue2); break;
default:
ErrorList.Current.AddError("Unsupported operator {0} for the Total Due.", _criteria.TotalDueOperator); break;
}
}
#endregion
#region CustomerStore
if (_criteria.CustomerStoreOperator != null)
{
switch (_criteria.CustomerStoreOperator)
{
case Operators.IsNull:
qry = qry.Where(o => o.CustomerStore == null); break;
case Operators.IsNotNull:
qry = qry.Where(o => o.CustomerStore != null); break;
case Operators.IsEqualTo:
qry = qry.Where(o => o.CustomerStore == _criteria.CustomerStore); break;
case Operators.IsNotEqualTo:
qry = qry.Where(o => o.CustomerStore != _criteria.CustomerStore); break;
case Operators.Contains:
qry = qry.Where(o => o.CustomerStore.Contains(_criteria.CustomerStore)); break;
case Operators.DoesNotContain:
qry = qry.Where(o => !o.CustomerStore.Contains(_criteria.CustomerStore)); break;
default:
ErrorList.Current.AddError("Unsupported operator {0} for the Customer Store.", _criteria.CustomerStoreOperator); break;
}
}
#endregion
#region CustomerName
if (_criteria.CustomerNameOperator != null)
{
switch (_criteria.CustomerNameOperator)
{
case Operators.IsNull:
qry = qry.Where(o => o.CustomerName == null); break;
case Operators.IsNotNull:
qry = qry.Where(o => o.CustomerName != null); break;
case Operators.IsEqualTo:
qry = qry.Where(o => o.CustomerName == _criteria.CustomerName); break;
case Operators.IsNotEqualTo:
qry = qry.Where(o => o.CustomerName != _criteria.CustomerName); break;
case Operators.Contains:
qry = qry.Where(o => o.CustomerName.Contains(_criteria.CustomerName)); break;
case Operators.DoesNotContain:
qry = qry.Where(o => !o.CustomerName.Contains(_criteria.CustomerName)); break;
default:
ErrorList.Current.AddError("Unsupported operator {0} for the Customer Name.", _criteria.CustomerNameOperator); break;
}
}
#endregion
#region TerritoryId
if (_criteria.TerritoryIdOperator != null)
{
switch (_criteria.TerritoryIdOperator)
{
case Operators.IsNull:
qry = qry.Where(o => o.TerritoryId == null); break;
case Operators.IsNotNull:
qry = qry.Where(o => o.TerritoryId != null); break;
case Operators.IsEqualTo:
qry = qry.Where(o => o.TerritoryId == _criteria.TerritoryId); break;
case Operators.IsNotEqualTo:
qry = qry.Where(o => o.TerritoryId != _criteria.TerritoryId); break;
default:
ErrorList.Current.AddError("Unsupported operator {0} for the Territory Id.", _criteria.TerritoryIdOperator); break;
}
}
#endregion
#region SalesPersonId
if (_criteria.SalesPersonIdOperator != null)
{
switch (_criteria.SalesPersonIdOperator)
{
case Operators.IsNull:
qry = qry.Where(o => o.SalesPersonId == null); break;
case Operators.IsNotNull:
qry = qry.Where(o => o.SalesPersonId != null); break;
case Operators.IsOneOf:
qry = qry.WhereIn(o => o.SalesPersonId, _criteria.SalesPersonId); break;
case Operators.IsNoneOf:
qry = qry.WhereNotIn(o => o.SalesPersonId, _criteria.SalesPersonId); break;
default:
ErrorList.Current.AddError("Unsupported operator {0} for the Sales Person Id.", _criteria.SalesPersonIdOperator); break;
}
}
#endregion
}
// CUSTOM_CODE_START: add custom filter criteria to the result query for ReadList operation below
// qry = qry.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
ErrorList.Current.AbortIfHasErrors(HttpStatusCode.BadRequest);
res = qry.ToList();
}
return res;
}
public virtual void Update(int _salesOrderId, SalesOrder_UpdateInput_Data _data)
{
using (AdventureWorksEntities ctx = new AdventureWorksEntities())
{
SalesOrder obj = ctx.SalesOrder.Find(_salesOrderId);
if (obj == null)
{
ErrorList.Current.CriticalError(HttpStatusCode.NotFound, "SalesOrder with id {0} not found", _salesOrderId);
}
var entry = ctx.Entry(obj);
entry.CurrentValues.SetValues(_data);
// CUSTOM_CODE_START: use the Customer input parameter of Update operation below
UpdateCustomer(ctx, obj, _data.Customer); // CUSTOM_CODE_END
// CUSTOM_CODE_START: use the Payment input parameter of Update operation below
UpdatePayment(ctx, obj, _data.Payment); // CUSTOM_CODE_END
// CUSTOM_CODE_START: use the Sales input parameter of Update operation below
UpdateSales(ctx, obj, _data.Sales); // CUSTOM_CODE_END
// CUSTOM_CODE_START: add custom code for Update operation below
obj.ModifiedDate = DateTime.Now;
// CUSTOM_CODE_END
ErrorList.Current.AbortIfHasErrors(HttpStatusCode.BadRequest);
ctx.SaveChanges();
}
}
public virtual SalesOrderDetail_CreateOutput Detail_Create(SalesOrderDetail_CreateInput _data)
{
SalesOrderDetail_CreateOutput res = new SalesOrderDetail_CreateOutput();
using (AdventureWorksEntities ctx = new AdventureWorksEntities())
{
EntityState state = EntityState.Added;
SalesOrderDetail obj = new SalesOrderDetail();
var entry = ctx.Entry(obj);
entry.State = state;
entry.CurrentValues.SetValues(_data);
obj.SalesOrderObject = ctx.SalesOrder.Find(_data.SalesOrderId);
if (obj.SalesOrderObject == null)
ErrorList.Current.AddError("Invalid value {0} for parameter SalesOrderId. Cannot find the corresponding SalesOrder object.", _data.SalesOrderId);
// CUSTOM_CODE_START: use the SpecialOfferId input parameter of Detail_Create operation below
// TODO: ??? = _data.SpecialOfferId; // CUSTOM_CODE_END
// CUSTOM_CODE_START: use the ProductId input parameter of Detail_Create operation below
// TODO: ??? = _data.ProductId; // CUSTOM_CODE_END
// CUSTOM_CODE_START: add custom code for Detail_Create operation below
// CUSTOM_CODE_END
ErrorList.Current.AbortIfHasErrors(HttpStatusCode.BadRequest);
ctx.SaveChanges();
ServiceUtil.CopyProperties(obj, res);
}
return res;
}
public DbContextController()
{
_context = new AdventureWorksEntities();
_lazyLoadingEnabled = _context.Configuration.LazyLoadingEnabled;
}
public CategoryController()
{
entities = new AdventureWorksEntities();
}
public ActionResult AllAddressAllProducts()
{
AllProductsAndAddresses all = new AllProductsAndAddresses();
using (AdventureWorksEntities data = new AdventureWorksEntities())
{
all.products = data.Products.ToList();
all.address = data.Addresses.ToList();
}
return View(all);
}
public virtual IEnumerable<Customer_ReadListOutput> ReadList(Customer_ReadListInput_Criteria _criteria)
{
IEnumerable<Customer_ReadListOutput> res = null;
using (AdventureWorksEntities ctx = new AdventureWorksEntities())
{
var src = from obj in ctx.Customer select obj;
#region Source filter
if (_criteria != null)
{
}
// CUSTOM_CODE_START: add custom filter criteria to the source query for ReadList operation below
// src = src.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
var qry = from obj in src
select new Customer_ReadListOutput() {
CustomerId = obj.CustomerId,
StoreId = obj.StoreIdObject.BusinessEntityId,
// CUSTOM_CODE_START: set the StoreName output parameter of ReadList operation below
StoreName = obj.StoreIdObject.Name, // CUSTOM_CODE_END
PersonId = obj.PersonIdObject.BusinessEntityId,
// CUSTOM_CODE_START: set the PersonName output parameter of ReadList operation below
PersonName = obj.PersonIdObject.LastName + ", " + obj.PersonIdObject.FirstName, // CUSTOM_CODE_END
AccountNumber = obj.AccountNumber,
TerritoryId = obj.TerritoryIdObject.TerritoryId,
};
#region Result filter
if (_criteria != null)
{
if (_criteria.TerritoryId != null)
qry = qry.Where(o => o.TerritoryId == _criteria.TerritoryId);
#region PersonName
if (_criteria.PersonNameOperator != null)
{
switch (_criteria.PersonNameOperator)
{
case Operators.IsNull:
qry = qry.Where(o => o.PersonName == null); break;
case Operators.IsNotNull:
qry = qry.Where(o => o.PersonName != null); break;
case Operators.IsEqualTo:
qry = qry.Where(o => o.PersonName == _criteria.PersonName); break;
case Operators.IsNotEqualTo:
qry = qry.Where(o => o.PersonName != _criteria.PersonName); break;
case Operators.Contains:
qry = qry.Where(o => o.PersonName.Contains(_criteria.PersonName)); break;
case Operators.DoesNotContain:
qry = qry.Where(o => !o.PersonName.Contains(_criteria.PersonName)); break;
default:
ErrorList.Current.AddError("Unsupported operator {0} for the Person Name.", _criteria.PersonNameOperator); break;
}
}
#endregion
#region StoreName
if (_criteria.StoreNameOperator != null)
{
switch (_criteria.StoreNameOperator)
{
case Operators.IsNull:
qry = qry.Where(o => o.StoreName == null); break;
case Operators.IsNotNull:
qry = qry.Where(o => o.StoreName != null); break;
case Operators.IsEqualTo:
qry = qry.Where(o => o.StoreName == _criteria.StoreName); break;
case Operators.IsNotEqualTo:
qry = qry.Where(o => o.StoreName != _criteria.StoreName); break;
case Operators.Contains:
qry = qry.Where(o => o.StoreName.Contains(_criteria.StoreName)); break;
case Operators.DoesNotContain:
qry = qry.Where(o => !o.StoreName.Contains(_criteria.StoreName)); break;
default:
ErrorList.Current.AddError("Unsupported operator {0} for the Store Name.", _criteria.StoreNameOperator); break;
}
}
#endregion
#region AccountNumber
if (_criteria.AccountNumberOperator != null)
{
switch (_criteria.AccountNumberOperator)
{
case Operators.IsEqualTo:
qry = qry.Where(o => o.AccountNumber == _criteria.AccountNumber); break;
case Operators.IsNotEqualTo:
qry = qry.Where(o => o.AccountNumber != _criteria.AccountNumber); break;
case Operators.Contains:
qry = qry.Where(o => o.AccountNumber.Contains(_criteria.AccountNumber)); break;
case Operators.DoesNotContain:
qry = qry.Where(o => !o.AccountNumber.Contains(_criteria.AccountNumber)); break;
default:
ErrorList.Current.AddError("Unsupported operator {0} for the Account Number.", _criteria.AccountNumberOperator); break;
}
}
#endregion
}
// CUSTOM_CODE_START: add custom filter criteria to the result query for ReadList operation below
// qry = qry.Where(o => o.FieldName == VALUE);
// CUSTOM_CODE_END
#endregion
ErrorList.Current.AbortIfHasErrors(HttpStatusCode.BadRequest);
res = qry.ToList();
}
return res;
}
protected void UpdateSales(AdventureWorksEntities ctx, SalesOrder obj, SalesInfo _data)
{
obj.TerritoryIdObject = ctx.SalesTerritory.Find(_data.TerritoryId);
if (_data.TerritoryId != null && obj.TerritoryIdObject == null)
ErrorList.Current.AddError("Cannot find Sales Territory with ID {0}.", _data.TerritoryId);
obj.SalesPersonIdObject = ctx.SalesPerson.Find(_data.SalesPersonId);
if (_data.SalesPersonId != null && obj.SalesPersonIdObject == null)
ErrorList.Current.AddError("Cannot find Sales Person with ID {0}.", _data.SalesPersonId);
// remove sales reason that are not in the provided list
obj.ReasonObjectList.Where(r => _data.SalesReason == null || !_data.SalesReason.Contains(r.SalesReasonId))
.ToList().ForEach(r => obj.ReasonObjectList.Remove(r));
if (_data.SalesReason != null)
{
// add sales reasons from provided list that don't exist yet
_data.SalesReason.Where(rId => obj.ReasonObjectList.Where(r => r.SalesReasonId == rId).ToList().Count == 0)
.ToList().ForEach(rId => obj.ReasonObjectList.Add(new SalesOrderReason()
{
SalesOrderObject = obj,
SalesReasonId = rId,
ModifiedDate = DateTime.Now
}));
}
}
protected void UpdatePayment(AdventureWorksEntities ctx, SalesOrder obj, PaymentUpdate _data)
{
obj.DueDate = _data.DueDate;
obj.CreditCardApprovalCode = _data.CreditCard.CreditCardApprovalCode;
obj.ShipMethodIdObject = ctx.ShipMethod.Find(_data.ShipMethodId);
if (obj.ShipMethodIdObject == null)
ErrorList.Current.AddError("Cannot find ShipMethod with ID {0}.", _data.ShipMethodId);
if (_data.CreditCard != null)
{
obj.CreditCardIdObject = ctx.CreditCard.Find(_data.CreditCard.CreditCardId);
if (obj.CreditCardIdObject == null)
ErrorList.Current.AddError("Cannot find CreditCard with ID {0}.",
_data.CreditCard.CreditCardId);
}
}
//[HttpPost]
public ActionResult Save(AddressModel model)
{
//throw new Exception("test");
if (ModelState.IsValid)
{
using (AdventureWorksEntities data = new AdventureWorksEntities())
{
Address adresa = new Address();
adresa.AddressLine1 = model.AddressLine1;
adresa.AddressLine2 = model.AddressLine2;
//data.SaveChanges();
}
}
return View();
}
public ActionResult ListByNumber(int houseNumber)
{
using (AdventureWorksEntities data = new AdventureWorksEntities())
{
string broj = houseNumber.ToString();
var addresses = data.Addresses.Where(p => p.AddressLine1.Contains(broj)).ToList();
return View(addresses);
}
}
