Entity Framework - Adding / Updating Records

In previous post we reviewed Entity Framework and how you can use Linq to entity to get the data from the database and bind it to a control.


In this post we will use the same project but go one step further - insert and update data using entity framework. Although this is rather simple example, you can still see the power of entity framework and how easy it is to perform database operations without writing much code.


In this example, instead of using read only text fields to load the address, we will load the addresses in a gridview which will allow us to add another address or edit an existing address.


I am using the same project from the previous post, so I already have entity framework objects configured. I created a new form with two controls - Combobox and a Gridview. When you run this project, combobox will load all the customers and selecting a name will load the selected customer's address(es) in the gridview

.
Running the Project

My grid is editable, so I can update an existing address or enter a new address. Once I modify an existing address or add a new address and click on Update, the code behind this button will save my changes and give me a confirmation message.

Let's review code.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
namespace EntiryFramework
{
    public partial class Main2 : Form
    {
        private OrdersEntities orders;
        public Main2()
        {
            InitializeComponent();
        }

        private void Main2_Load(object sender, EventArgs e)
        {
            orders = new OrdersEntities();
        var customerQuery = from d in orders.Customers.Include("CustomerAddresses")
                            orderby d.LastName
                            select d;
            try
            {
                // Bind the ComboBox control to the query, 
                // which is executed during data binding.
                // To prevent the query from being executed multiple 
                //times during binding, it is recommended to bind 
               //controls to the result of the Execute method. 
                this.customerList.DisplayMember = "LastName";
                this.customerList.ValueMember = "CustomerID";
                this.customerList.DataSource = 
                ((ObjectQuery)customerQuery).Execute(MergeOption.AppendOnly);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        
    private void customerList_SelectedIndexChanged_1(object sender, EventArgs e)
        {
            try
            { 
          //lets get the addresses that are associated with the selected customer
               Customer customer = (Customer)this.customerList.SelectedItem;

            Addresses.DataSource = customer.CustomerAddresses;
            Addresses.Columns["CustomerID"].Visible = false;
            Addresses.Columns["AddressID"].Visible = false;
            Addresses.Columns["Customer"].Visible = false;
            Addresses.AllowUserToDeleteRows = false;
            Addresses.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void Close_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void saveChanges_Click(object sender, EventArgs e)
        {
            try
            {

                // Save  changes to the database, 
                // display a message, and refresh the form.
                orders.SaveChanges();
                MessageBox.Show("Changes saved to the database.");
                this.Refresh();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
    }
}

Notice the code under "saveChanges_Click" method. orders.SaveChanges() is all it takes to save your changes - whether modifications to an existing address or adding new addresses.


For this to work, be sure your tables have a primary key. Without a primary key, this will not work. Also, you may get an error if your entity is mapped to a customized query or a view rather than a table.


Entity Framework allows you to map your database schema and have the framework generate the plumbing for you. But for this to truly work, you must have proper keys/referential integrity in your database tables and relationships between them.


Thank you.

Entity Framework - Part II

In previous post we created a simple winform project and then generated an entity framework data model (EDM) using a small Orders database. Today, we will use the same project and query the data using entity framework from the underlying database and bind it to an element. If you haven't reviewed previous post, I strongly suggest you do so now.

In this example, we will use some of the built in methods such as List to get a list of customers and bind it to the dropdown list.

We will also use Linq to entities to get a list of addresses that are associated with the selected customer and load the address in read only text fields.

Example - 

using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Objects;
using System.Data.Objects.DataClasses;

namespace EntiryFramework
{
    public partial class Main : Form
    {
        private OrdersEntities orders;
        public Main()
        {
            InitializeComponent();
        }

        private void Close_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void Main_Load(object sender, EventArgs e)
        {

            //Initialize the ObjectContext
            orders = new OrdersEntities();

            //Load all customers 

            List<Customer> customerList = orders.Customers.ToList();
            try
            {
                //bind to the database
                this.customerList.DisplayMember = "LastName";
                this.customerList.ValueMember = "CustomerID";
                this.customerList.DataSource = customerList;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }



        }

        private void customerList_SelectedIndexChanged(object sender, EventArgs e)
        {
            //lets get the addresses that are associated with the selected customer
            int customerID = (int)this.customerList.SelectedValue;
           
            IQueryable<CustomerAddress> customerAddress = orders.Customers
                .Where(d => d.CustomerID == customerID)
                .SelectMany(d => d.CustomerAddresses);
            foreach (CustomerAddress address in customerAddress)
            {
                txtAddress1.Text = address.Address1;
                txtAddress2.Text = address.Address2;
                txtCity.Text = address.City;
                txtState.Text = address.State;
                txtZip.Text = address.ZipCode;
            }
           
        }
    }
}

When you run this application, you will see something like this and as you change the customer name in the drop down list, the address will change.

In this example I used the List() method to load all the customers from the database and then used Linq to entities to filter the list of addresses for the selected customer. I could have used Linq to entities to load all customers, doing something like this...

var customerQuery = from d in orders.Customers
                                  orderby d.LastName
                                  select d;
            try
            {
                // Bind the ComboBox control to the query, 
                // which is executed during data binding.
                // To prevent the query from being executed multiple times during binding, 
                // it is recommended to bind controls to the result of the Execute method. 
                this.customerList.DisplayMember = "LastName";
                this.customerList.ValueMember = "CustomerID";
                this.customerList.DataSource = ((ObjectQuery)customerQuery)
                                               .Execute(MergeOption.AppendOnly);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

In the next post we will update a customer using entity framework.

Thank you.

Entity Framework - Part I

In previous post we touched briefly on entity framework and the benefits it provides. In this post we will walk through the process of creating an entity data model object in our project and then in future post use it to create a functional project. If you have worked with typed datasets, you will find the process very similar when it comes to generating the EDM files, but in my view, entity framework is much more powerful and simpler to use than typed datasets.

Lets create a test project and then create an entity data model file. We will use a sample database that I have created in my test environment.

We will create a simple Winform application and add a combobox to display all the customers in the database.

1. Add a Winform and a combo box to this form.

Add Entity Data Model
1. Right click on the Project and Add new Item. Select ADO.NET Entity Data Model

2. Select Generate from database and click on Next

3. Select the server and the database and click on OK

4.Make sure to check "Save Entity Connection Settings in App.Config" and pick a name

5. Click on Finish and a new EDM file is generated with extension edmx and a code behind file. 
The edmx file looks like your database diagram (very similar to axd file)

6. If you open the code behind file, you will notice the framework has already abstracted your tables as classes and created a relationship between them. 

//------------------------------------------------------------------------------
// <auto-generated>
//    This code was generated from a template.
//
//    Manual changes to this file may cause unexpected behavior in your application.
//    Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

using System;
using System.ComponentModel;
using System.Data.EntityClient;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Linq;
using System.Runtime.Serialization;
using System.Xml.Serialization;

[assembly: EdmSchemaAttribute()]
#region EDM Relationship Metadata

[assembly: EdmRelationshipAttribute("OrdersModel", "FK_CustomerAddress_Customers", "Customers", System.Data.Metadata.Edm.RelationshipMultiplicity.One, typeof(EntiryFramework.Customer), "CustomerAddress", System.Data.Metadata.Edm.RelationshipMultiplicity.Many, typeof(EntiryFramework.CustomerAddress), true)]
[assembly: EdmRelationshipAttribute("OrdersModel", "FK_Orders_Customers", "Customers", System.Data.Metadata.Edm.RelationshipMultiplicity.One, typeof(EntiryFramework.Customer), "Orders", System.Data.Metadata.Edm.RelationshipMultiplicity.Many, typeof(EntiryFramework.Order), true)]
[assembly: EdmRelationshipAttribute("OrdersModel", "FK_OrderDetails_Items", "Items", System.Data.Metadata.Edm.RelationshipMultiplicity.One, typeof(EntiryFramework.Item), "OrderDetails", System.Data.Metadata.Edm.RelationshipMultiplicity.Many, typeof(EntiryFramework.OrderDetail), true)]
[assembly: EdmRelationshipAttribute("OrdersModel", "FK_OrderDetails_Orders", "Orders", System.Data.Metadata.Edm.RelationshipMultiplicity.One, typeof(EntiryFramework.Order), "OrderDetails", System.Data.Metadata.Edm.RelationshipMultiplicity.Many, typeof(EntiryFramework.OrderDetail), true)]
[assembly: EdmRelationshipAttribute("OrdersModel", "FK_Orders_SalesPerson", "SalesPerson", System.Data.Metadata.Edm.RelationshipMultiplicity.ZeroOrOne, typeof(EntiryFramework.SalesPerson), "Orders", System.Data.Metadata.Edm.RelationshipMultiplicity.Many, typeof(EntiryFramework.Order), true)]

#endregion

namespace EntiryFramework
{
    #region Contexts
    
    /// <summary>
    /// No Metadata Documentation available.
    /// </summary>
    public partial class OrdersEntities : ObjectContext
    {
        #region Constructors
    
        /// <summary>
        /// Initializes a new OrdersEntities object using the connection string found in the 'OrdersEntities' section of the application configuration file.
        /// </summary>
        public OrdersEntities() : base("name=OrdersEntities", "OrdersEntities")
        {
            this.ContextOptions.LazyLoadingEnabled = true;
            OnContextCreated();
        }
    
        /// <summary>
        /// Initialize a new OrdersEntities object.
        /// </summary>
        public OrdersEntities(string connectionString) : base(connectionString, "OrdersEntities")
        {
            this.ContextOptions.LazyLoadingEnabled = true;
            OnContextCreated();
        }
    
        /// <summary>
        /// Initialize a new OrdersEntities object.
        /// </summary>
        public OrdersEntities(EntityConnection connection) : base(connection, "OrdersEntities")
        {
            this.ContextOptions.LazyLoadingEnabled = true;
            OnContextCreated();
        }
    
        #endregion
    
        #region Partial Methods
    
        partial void OnContextCreated();
    
        #endregion
    
        #region ObjectSet Properties
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        public ObjectSet<CustomerAddress> CustomerAddresses
        {
            get
            {
                if ((_CustomerAddresses == null))
                {
                    _CustomerAddresses = base.CreateObjectSet<CustomerAddress>("CustomerAddresses");
                }
                return _CustomerAddresses;
            }
        }
        private ObjectSet<CustomerAddress> _CustomerAddresses;
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        public ObjectSet<Customer> Customers
        {
            get
            {
                if ((_Customers == null))
                {
                    _Customers = base.CreateObjectSet<Customer>("Customers");
                }
                return _Customers;
            }
        }
        private ObjectSet<Customer> _Customers;
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        public ObjectSet<Item> Items
        {
            get
            {
                if ((_Items == null))
                {
                    _Items = base.CreateObjectSet<Item>("Items");
                }
                return _Items;
            }
        }
        private ObjectSet<Item> _Items;
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        public ObjectSet<OrderDetail> OrderDetails
        {
            get
            {
                if ((_OrderDetails == null))
                {
                    _OrderDetails = base.CreateObjectSet<OrderDetail>("OrderDetails");
                }
                return _OrderDetails;
            }
        }
        private ObjectSet<OrderDetail> _OrderDetails;
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        public ObjectSet<Order> Orders
        {
            get
            {
                if ((_Orders == null))
                {
                    _Orders = base.CreateObjectSet<Order>("Orders");
                }
                return _Orders;
            }
        }
        private ObjectSet<Order> _Orders;
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        public ObjectSet<SalesPerson> SalesPersons
        {
            get
            {
                if ((_SalesPersons == null))
                {
                    _SalesPersons = base.CreateObjectSet<SalesPerson>("SalesPersons");
                }
                return _SalesPersons;
            }
        }
        private ObjectSet<SalesPerson> _SalesPersons;
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        public ObjectSet<sysdiagram> sysdiagrams
        {
            get
            {
                if ((_sysdiagrams == null))
                {
                    _sysdiagrams = base.CreateObjectSet<sysdiagram>("sysdiagrams");
                }
                return _sysdiagrams;
            }
        }
        private ObjectSet<sysdiagram> _sysdiagrams;

        #endregion

        #region AddTo Methods
    
        /// <summary>
        /// Deprecated Method for adding a new object to the CustomerAddresses EntitySet. Consider using the .Add method of the associated ObjectSet&lt;T&gt; property instead.
        /// </summary>
        public void AddToCustomerAddresses(CustomerAddress customerAddress)
        {
            base.AddObject("CustomerAddresses", customerAddress);
        }
    
        /// <summary>
        /// Deprecated Method for adding a new object to the Customers EntitySet. Consider using the .Add method of the associated ObjectSet&lt;T&gt; property instead.
        /// </summary>
        public void AddToCustomers(Customer customer)
        {
            base.AddObject("Customers", customer);
        }
    
        /// <summary>
        /// Deprecated Method for adding a new object to the Items EntitySet. Consider using the .Add method of the associated ObjectSet&lt;T&gt; property instead.
        /// </summary>
        public void AddToItems(Item item)
        {
            base.AddObject("Items", item);
        }
    
        /// <summary>
        /// Deprecated Method for adding a new object to the OrderDetails EntitySet. Consider using the .Add method of the associated ObjectSet&lt;T&gt; property instead.
        /// </summary>
        public void AddToOrderDetails(OrderDetail orderDetail)
        {
            base.AddObject("OrderDetails", orderDetail);
        }
    
        /// <summary>
        /// Deprecated Method for adding a new object to the Orders EntitySet. Consider using the .Add method of the associated ObjectSet&lt;T&gt; property instead.
        /// </summary>
        public void AddToOrders(Order order)
        {
            base.AddObject("Orders", order);
        }
    
        /// <summary>
        /// Deprecated Method for adding a new object to the SalesPersons EntitySet. Consider using the .Add method of the associated ObjectSet&lt;T&gt; property instead.
        /// </summary>
        public void AddToSalesPersons(SalesPerson salesPerson)
        {
            base.AddObject("SalesPersons", salesPerson);
        }
    
        /// <summary>
        /// Deprecated Method for adding a new object to the sysdiagrams EntitySet. Consider using the .Add method of the associated ObjectSet&lt;T&gt; property instead.
        /// </summary>
        public void AddTosysdiagrams(sysdiagram sysdiagram)
        {
            base.AddObject("sysdiagrams", sysdiagram);
        }

        #endregion

    }

    #endregion

    #region Entities
    
    /// <summary>
    /// No Metadata Documentation available.
    /// </summary>
    [EdmEntityTypeAttribute(NamespaceName="OrdersModel", Name="Customer")]
    [Serializable()]
    [DataContractAttribute(IsReference=true)]
    public partial class Customer : EntityObject
    {
        #region Factory Method
    
        /// <summary>
        /// Create a new Customer object.
        /// </summary>
        /// <param name="customerID">Initial value of the CustomerID property.</param>
        public static Customer CreateCustomer(global::System.Int32 customerID)
        {
            Customer customer = new Customer();
            customer.CustomerID = customerID;
            return customer;
        }

        #endregion

        #region Primitive Properties
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=true, IsNullable=false)]
        [DataMemberAttribute()]
        public global::System.Int32 CustomerID
        {
            get
            {
                return _CustomerID;
            }
            set
            {
                if (_CustomerID != value)
                {
                    OnCustomerIDChanging(value);
                    ReportPropertyChanging("CustomerID");
                    _CustomerID = StructuralObject.SetValidValue(value);
                    ReportPropertyChanged("CustomerID");
                    OnCustomerIDChanged();
                }
            }
        }
        private global::System.Int32 _CustomerID;
        partial void OnCustomerIDChanging(global::System.Int32 value);
        partial void OnCustomerIDChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public global::System.String FirstName
        {
            get
            {
                return _FirstName;
            }
            set
            {
                OnFirstNameChanging(value);
                ReportPropertyChanging("FirstName");
                _FirstName = StructuralObject.SetValidValue(value, true);
                ReportPropertyChanged("FirstName");
                OnFirstNameChanged();
            }
        }
        private global::System.String _FirstName;
        partial void OnFirstNameChanging(global::System.String value);
        partial void OnFirstNameChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public global::System.String LastName
        {
            get
            {
                return _LastName;
            }
            set
            {
                OnLastNameChanging(value);
                ReportPropertyChanging("LastName");
                _LastName = StructuralObject.SetValidValue(value, true);
                ReportPropertyChanged("LastName");
                OnLastNameChanged();
            }
        }
        private global::System.String _LastName;
        partial void OnLastNameChanging(global::System.String value);
        partial void OnLastNameChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public global::System.String EmailAddress
        {
            get
            {
                return _EmailAddress;
            }
            set
            {
                OnEmailAddressChanging(value);
                ReportPropertyChanging("EmailAddress");
                _EmailAddress = StructuralObject.SetValidValue(value, true);
                ReportPropertyChanged("EmailAddress");
                OnEmailAddressChanged();
            }
        }
        private global::System.String _EmailAddress;
        partial void OnEmailAddressChanging(global::System.String value);
        partial void OnEmailAddressChanged();

        #endregion

    
        #region Navigation Properties
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [XmlIgnoreAttribute()]
        [SoapIgnoreAttribute()]
        [DataMemberAttribute()]
        [EdmRelationshipNavigationPropertyAttribute("OrdersModel", "FK_CustomerAddress_Customers", "CustomerAddress")]
        public EntityCollection<CustomerAddress> CustomerAddresses
        {
            get
            {
                return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedCollection<CustomerAddress>("OrdersModel.FK_CustomerAddress_Customers", "CustomerAddress");
            }
            set
            {
                if ((value != null))
                {
                    ((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedCollection<CustomerAddress>("OrdersModel.FK_CustomerAddress_Customers", "CustomerAddress", value);
                }
            }
        }
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [XmlIgnoreAttribute()]
        [SoapIgnoreAttribute()]
        [DataMemberAttribute()]
        [EdmRelationshipNavigationPropertyAttribute("OrdersModel", "FK_Orders_Customers", "Orders")]
        public EntityCollection<Order> Orders
        {
            get
            {
                return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedCollection<Order>("OrdersModel.FK_Orders_Customers", "Orders");
            }
            set
            {
                if ((value != null))
                {
                    ((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedCollection<Order>("OrdersModel.FK_Orders_Customers", "Orders", value);
                }
            }
        }

        #endregion

    }
    
    /// <summary>
    /// No Metadata Documentation available.
    /// </summary>
    [EdmEntityTypeAttribute(NamespaceName="OrdersModel", Name="CustomerAddress")]
    [Serializable()]
    [DataContractAttribute(IsReference=true)]
    public partial class CustomerAddress : EntityObject
    {
        #region Factory Method
    
        /// <summary>
        /// Create a new CustomerAddress object.
        /// </summary>
        /// <param name="addressID">Initial value of the AddressID property.</param>
        /// <param name="customerID">Initial value of the CustomerID property.</param>
        public static CustomerAddress CreateCustomerAddress(global::System.Int32 addressID, global::System.Int32 customerID)
        {
            CustomerAddress customerAddress = new CustomerAddress();
            customerAddress.AddressID = addressID;
            customerAddress.CustomerID = customerID;
            return customerAddress;
        }

        #endregion

        #region Primitive Properties
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=true, IsNullable=false)]
        [DataMemberAttribute()]
        public global::System.Int32 AddressID
        {
            get
            {
                return _AddressID;
            }
            set
            {
                if (_AddressID != value)
                {
                    OnAddressIDChanging(value);
                    ReportPropertyChanging("AddressID");
                    _AddressID = StructuralObject.SetValidValue(value);
                    ReportPropertyChanged("AddressID");
                    OnAddressIDChanged();
                }
            }
        }
        private global::System.Int32 _AddressID;
        partial void OnAddressIDChanging(global::System.Int32 value);
        partial void OnAddressIDChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=true, IsNullable=false)]
        [DataMemberAttribute()]
        public global::System.Int32 CustomerID
        {
            get
            {
                return _CustomerID;
            }
            set
            {
                if (_CustomerID != value)
                {
                    OnCustomerIDChanging(value);
                    ReportPropertyChanging("CustomerID");
                    _CustomerID = StructuralObject.SetValidValue(value);
                    ReportPropertyChanged("CustomerID");
                    OnCustomerIDChanged();
                }
            }
        }
        private global::System.Int32 _CustomerID;
        partial void OnCustomerIDChanging(global::System.Int32 value);
        partial void OnCustomerIDChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public global::System.String Address1
        {
            get
            {
                return _Address1;
            }
            set
            {
                OnAddress1Changing(value);
                ReportPropertyChanging("Address1");
                _Address1 = StructuralObject.SetValidValue(value, true);
                ReportPropertyChanged("Address1");
                OnAddress1Changed();
            }
        }
        private global::System.String _Address1;
        partial void OnAddress1Changing(global::System.String value);
        partial void OnAddress1Changed();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public global::System.String Address2
        {
            get
            {
                return _Address2;
            }
            set
            {
                OnAddress2Changing(value);
                ReportPropertyChanging("Address2");
                _Address2 = StructuralObject.SetValidValue(value, true);
                ReportPropertyChanged("Address2");
                OnAddress2Changed();
            }
        }
        private global::System.String _Address2;
        partial void OnAddress2Changing(global::System.String value);
        partial void OnAddress2Changed();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public global::System.String City
        {
            get
            {
                return _City;
            }
            set
            {
                OnCityChanging(value);
                ReportPropertyChanging("City");
                _City = StructuralObject.SetValidValue(value, true);
                ReportPropertyChanged("City");
                OnCityChanged();
            }
        }
        private global::System.String _City;
        partial void OnCityChanging(global::System.String value);
        partial void OnCityChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public global::System.String State
        {
            get
            {
                return _State;
            }
            set
            {
                OnStateChanging(value);
                ReportPropertyChanging("State");
                _State = StructuralObject.SetValidValue(value, true);
                ReportPropertyChanged("State");
                OnStateChanged();
            }
        }
        private global::System.String _State;
        partial void OnStateChanging(global::System.String value);
        partial void OnStateChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public global::System.String ZipCode
        {
            get
            {
                return _ZipCode;
            }
            set
            {
                OnZipCodeChanging(value);
                ReportPropertyChanging("ZipCode");
                _ZipCode = StructuralObject.SetValidValue(value, true);
                ReportPropertyChanged("ZipCode");
                OnZipCodeChanged();
            }
        }
        private global::System.String _ZipCode;
        partial void OnZipCodeChanging(global::System.String value);
        partial void OnZipCodeChanged();

        #endregion

    
        #region Navigation Properties
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [XmlIgnoreAttribute()]
        [SoapIgnoreAttribute()]
        [DataMemberAttribute()]
        [EdmRelationshipNavigationPropertyAttribute("OrdersModel", "FK_CustomerAddress_Customers", "Customers")]
        public Customer Customer
        {
            get
            {
                return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedReference<Customer>("OrdersModel.FK_CustomerAddress_Customers", "Customers").Value;
            }
            set
            {
                ((IEntityWithRelationships)this).RelationshipManager.GetRelatedReference<Customer>("OrdersModel.FK_CustomerAddress_Customers", "Customers").Value = value;
            }
        }
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [BrowsableAttribute(false)]
        [DataMemberAttribute()]
        public EntityReference<Customer> CustomerReference
        {
            get
            {
                return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedReference<Customer>("OrdersModel.FK_CustomerAddress_Customers", "Customers");
            }
            set
            {
                if ((value != null))
                {
                    ((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedReference<Customer>("OrdersModel.FK_CustomerAddress_Customers", "Customers", value);
                }
            }
        }

        #endregion

    }
    
    /// <summary>
    /// No Metadata Documentation available.
    /// </summary>
    [EdmEntityTypeAttribute(NamespaceName="OrdersModel", Name="Item")]
    [Serializable()]
    [DataContractAttribute(IsReference=true)]
    public partial class Item : EntityObject
    {
        #region Factory Method
    
        /// <summary>
        /// Create a new Item object.
        /// </summary>
        /// <param name="itemID">Initial value of the ItemID property.</param>
        public static Item CreateItem(global::System.Int32 itemID)
        {
            Item item = new Item();
            item.ItemID = itemID;
            return item;
        }

        #endregion

        #region Primitive Properties
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=true, IsNullable=false)]
        [DataMemberAttribute()]
        public global::System.Int32 ItemID
        {
            get
            {
                return _ItemID;
            }
            set
            {
                if (_ItemID != value)
                {
                    OnItemIDChanging(value);
                    ReportPropertyChanging("ItemID");
                    _ItemID = StructuralObject.SetValidValue(value);
                    ReportPropertyChanged("ItemID");
                    OnItemIDChanged();
                }
            }
        }
        private global::System.Int32 _ItemID;
        partial void OnItemIDChanging(global::System.Int32 value);
        partial void OnItemIDChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public global::System.String Description
        {
            get
            {
                return _Description;
            }
            set
            {
                OnDescriptionChanging(value);
                ReportPropertyChanging("Description");
                _Description = StructuralObject.SetValidValue(value, true);
                ReportPropertyChanged("Description");
                OnDescriptionChanged();
            }
        }
        private global::System.String _Description;
        partial void OnDescriptionChanging(global::System.String value);
        partial void OnDescriptionChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public Nullable<global::System.Int32> QuantityOnHand
        {
            get
            {
                return _QuantityOnHand;
            }
            set
            {
                OnQuantityOnHandChanging(value);
                ReportPropertyChanging("QuantityOnHand");
                _QuantityOnHand = StructuralObject.SetValidValue(value);
                ReportPropertyChanged("QuantityOnHand");
                OnQuantityOnHandChanged();
            }
        }
        private Nullable<global::System.Int32> _QuantityOnHand;
        partial void OnQuantityOnHandChanging(Nullable<global::System.Int32> value);
        partial void OnQuantityOnHandChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public Nullable<global::System.Decimal> SalesPrice
        {
            get
            {
                return _SalesPrice;
            }
            set
            {
                OnSalesPriceChanging(value);
                ReportPropertyChanging("SalesPrice");
                _SalesPrice = StructuralObject.SetValidValue(value);
                ReportPropertyChanged("SalesPrice");
                OnSalesPriceChanged();
            }
        }
        private Nullable<global::System.Decimal> _SalesPrice;
        partial void OnSalesPriceChanging(Nullable<global::System.Decimal> value);
        partial void OnSalesPriceChanged();

        #endregion

    
        #region Navigation Properties
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [XmlIgnoreAttribute()]
        [SoapIgnoreAttribute()]
        [DataMemberAttribute()]
        [EdmRelationshipNavigationPropertyAttribute("OrdersModel", "FK_OrderDetails_Items", "OrderDetails")]
        public EntityCollection<OrderDetail> OrderDetails
        {
            get
            {
                return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedCollection<OrderDetail>("OrdersModel.FK_OrderDetails_Items", "OrderDetails");
            }
            set
            {
                if ((value != null))
                {
                    ((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedCollection<OrderDetail>("OrdersModel.FK_OrderDetails_Items", "OrderDetails", value);
                }
            }
        }

        #endregion

    }
    
    /// <summary>
    /// No Metadata Documentation available.
    /// </summary>
    [EdmEntityTypeAttribute(NamespaceName="OrdersModel", Name="Order")]
    [Serializable()]
    [DataContractAttribute(IsReference=true)]
    public partial class Order : EntityObject
    {
        #region Factory Method
    
        /// <summary>
        /// Create a new Order object.
        /// </summary>
        /// <param name="orderID">Initial value of the OrderID property.</param>
        /// <param name="customerID">Initial value of the CustomerID property.</param>
        public static Order CreateOrder(global::System.Int32 orderID, global::System.Int32 customerID)
        {
            Order order = new Order();
            order.OrderID = orderID;
            order.CustomerID = customerID;
            return order;
        }

        #endregion

        #region Primitive Properties
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=true, IsNullable=false)]
        [DataMemberAttribute()]
        public global::System.Int32 OrderID
        {
            get
            {
                return _OrderID;
            }
            set
            {
                if (_OrderID != value)
                {
                    OnOrderIDChanging(value);
                    ReportPropertyChanging("OrderID");
                    _OrderID = StructuralObject.SetValidValue(value);
                    ReportPropertyChanged("OrderID");
                    OnOrderIDChanged();
                }
            }
        }
        private global::System.Int32 _OrderID;
        partial void OnOrderIDChanging(global::System.Int32 value);
        partial void OnOrderIDChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
        [DataMemberAttribute()]
        public global::System.Int32 CustomerID
        {
            get
            {
                return _CustomerID;
            }
            set
            {
                OnCustomerIDChanging(value);
                ReportPropertyChanging("CustomerID");
                _CustomerID = StructuralObject.SetValidValue(value);
                ReportPropertyChanged("CustomerID");
                OnCustomerIDChanged();
            }
        }
        private global::System.Int32 _CustomerID;
        partial void OnCustomerIDChanging(global::System.Int32 value);
        partial void OnCustomerIDChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public Nullable<global::System.Int32> SalesPersonID
        {
            get
            {
                return _SalesPersonID;
            }
            set
            {
                OnSalesPersonIDChanging(value);
                ReportPropertyChanging("SalesPersonID");
                _SalesPersonID = StructuralObject.SetValidValue(value);
                ReportPropertyChanged("SalesPersonID");
                OnSalesPersonIDChanged();
            }
        }
        private Nullable<global::System.Int32> _SalesPersonID;
        partial void OnSalesPersonIDChanging(Nullable<global::System.Int32> value);
        partial void OnSalesPersonIDChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public Nullable<global::System.Decimal> OrderAmount
        {
            get
            {
                return _OrderAmount;
            }
            set
            {
                OnOrderAmountChanging(value);
                ReportPropertyChanging("OrderAmount");
                _OrderAmount = StructuralObject.SetValidValue(value);
                ReportPropertyChanged("OrderAmount");
                OnOrderAmountChanged();
            }
        }
        private Nullable<global::System.Decimal> _OrderAmount;
        partial void OnOrderAmountChanging(Nullable<global::System.Decimal> value);
        partial void OnOrderAmountChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public Nullable<global::System.Decimal> SalesTax
        {
            get
            {
                return _SalesTax;
            }
            set
            {
                OnSalesTaxChanging(value);
                ReportPropertyChanging("SalesTax");
                _SalesTax = StructuralObject.SetValidValue(value);
                ReportPropertyChanged("SalesTax");
                OnSalesTaxChanged();
            }
        }
        private Nullable<global::System.Decimal> _SalesTax;
        partial void OnSalesTaxChanging(Nullable<global::System.Decimal> value);
        partial void OnSalesTaxChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public Nullable<global::System.Int32> OrderStatusID
        {
            get
            {
                return _OrderStatusID;
            }
            set
            {
                OnOrderStatusIDChanging(value);
                ReportPropertyChanging("OrderStatusID");
                _OrderStatusID = StructuralObject.SetValidValue(value);
                ReportPropertyChanged("OrderStatusID");
                OnOrderStatusIDChanged();
            }
        }
        private Nullable<global::System.Int32> _OrderStatusID;
        partial void OnOrderStatusIDChanging(Nullable<global::System.Int32> value);
        partial void OnOrderStatusIDChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public Nullable<global::System.DateTime> OrderDate
        {
            get
            {
                return _OrderDate;
            }
            set
            {
                OnOrderDateChanging(value);
                ReportPropertyChanging("OrderDate");
                _OrderDate = StructuralObject.SetValidValue(value);
                ReportPropertyChanged("OrderDate");
                OnOrderDateChanged();
            }
        }
        private Nullable<global::System.DateTime> _OrderDate;
        partial void OnOrderDateChanging(Nullable<global::System.DateTime> value);
        partial void OnOrderDateChanged();

        #endregion

    
        #region Navigation Properties
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [XmlIgnoreAttribute()]
        [SoapIgnoreAttribute()]
        [DataMemberAttribute()]
        [EdmRelationshipNavigationPropertyAttribute("OrdersModel", "FK_Orders_Customers", "Customers")]
        public Customer Customer
        {
            get
            {
                return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedReference<Customer>("OrdersModel.FK_Orders_Customers", "Customers").Value;
            }
            set
            {
                ((IEntityWithRelationships)this).RelationshipManager.GetRelatedReference<Customer>("OrdersModel.FK_Orders_Customers", "Customers").Value = value;
            }
        }
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [BrowsableAttribute(false)]
        [DataMemberAttribute()]
        public EntityReference<Customer> CustomerReference
        {
            get
            {
                return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedReference<Customer>("OrdersModel.FK_Orders_Customers", "Customers");
            }
            set
            {
                if ((value != null))
                {
                    ((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedReference<Customer>("OrdersModel.FK_Orders_Customers", "Customers", value);
                }
            }
        }
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [XmlIgnoreAttribute()]
        [SoapIgnoreAttribute()]
        [DataMemberAttribute()]
        [EdmRelationshipNavigationPropertyAttribute("OrdersModel", "FK_OrderDetails_Orders", "OrderDetails")]
        public EntityCollection<OrderDetail> OrderDetails
        {
            get
            {
                return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedCollection<OrderDetail>("OrdersModel.FK_OrderDetails_Orders", "OrderDetails");
            }
            set
            {
                if ((value != null))
                {
                    ((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedCollection<OrderDetail>("OrdersModel.FK_OrderDetails_Orders", "OrderDetails", value);
                }
            }
        }
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [XmlIgnoreAttribute()]
        [SoapIgnoreAttribute()]
        [DataMemberAttribute()]
        [EdmRelationshipNavigationPropertyAttribute("OrdersModel", "FK_Orders_SalesPerson", "SalesPerson")]
        public SalesPerson SalesPerson
        {
            get
            {
                return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedReference<SalesPerson>("OrdersModel.FK_Orders_SalesPerson", "SalesPerson").Value;
            }
            set
            {
                ((IEntityWithRelationships)this).RelationshipManager.GetRelatedReference<SalesPerson>("OrdersModel.FK_Orders_SalesPerson", "SalesPerson").Value = value;
            }
        }
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [BrowsableAttribute(false)]
        [DataMemberAttribute()]
        public EntityReference<SalesPerson> SalesPersonReference
        {
            get
            {
                return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedReference<SalesPerson>("OrdersModel.FK_Orders_SalesPerson", "SalesPerson");
            }
            set
            {
                if ((value != null))
                {
                    ((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedReference<SalesPerson>("OrdersModel.FK_Orders_SalesPerson", "SalesPerson", value);
                }
            }
        }

        #endregion

    }
    
    /// <summary>
    /// No Metadata Documentation available.
    /// </summary>
    [EdmEntityTypeAttribute(NamespaceName="OrdersModel", Name="OrderDetail")]
    [Serializable()]
    [DataContractAttribute(IsReference=true)]
    public partial class OrderDetail : EntityObject
    {
        #region Factory Method
    
        /// <summary>
        /// Create a new OrderDetail object.
        /// </summary>
        /// <param name="orderDetailID">Initial value of the OrderDetailID property.</param>
        /// <param name="orderID">Initial value of the OrderID property.</param>
        /// <param name="itemID">Initial value of the ItemID property.</param>
        /// <param name="quantity">Initial value of the Quantity property.</param>
        public static OrderDetail CreateOrderDetail(global::System.Int32 orderDetailID, global::System.Int32 orderID, global::System.Int32 itemID, global::System.Int32 quantity)
        {
            OrderDetail orderDetail = new OrderDetail();
            orderDetail.OrderDetailID = orderDetailID;
            orderDetail.OrderID = orderID;
            orderDetail.ItemID = itemID;
            orderDetail.Quantity = quantity;
            return orderDetail;
        }

        #endregion

        #region Primitive Properties
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=true, IsNullable=false)]
        [DataMemberAttribute()]
        public global::System.Int32 OrderDetailID
        {
            get
            {
                return _OrderDetailID;
            }
            set
            {
                if (_OrderDetailID != value)
                {
                    OnOrderDetailIDChanging(value);
                    ReportPropertyChanging("OrderDetailID");
                    _OrderDetailID = StructuralObject.SetValidValue(value);
                    ReportPropertyChanged("OrderDetailID");
                    OnOrderDetailIDChanged();
                }
            }
        }
        private global::System.Int32 _OrderDetailID;
        partial void OnOrderDetailIDChanging(global::System.Int32 value);
        partial void OnOrderDetailIDChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
        [DataMemberAttribute()]
        public global::System.Int32 OrderID
        {
            get
            {
                return _OrderID;
            }
            set
            {
                OnOrderIDChanging(value);
                ReportPropertyChanging("OrderID");
                _OrderID = StructuralObject.SetValidValue(value);
                ReportPropertyChanged("OrderID");
                OnOrderIDChanged();
            }
        }
        private global::System.Int32 _OrderID;
        partial void OnOrderIDChanging(global::System.Int32 value);
        partial void OnOrderIDChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
        [DataMemberAttribute()]
        public global::System.Int32 ItemID
        {
            get
            {
                return _ItemID;
            }
            set
            {
                OnItemIDChanging(value);
                ReportPropertyChanging("ItemID");
                _ItemID = StructuralObject.SetValidValue(value);
                ReportPropertyChanged("ItemID");
                OnItemIDChanged();
            }
        }
        private global::System.Int32 _ItemID;
        partial void OnItemIDChanging(global::System.Int32 value);
        partial void OnItemIDChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
        [DataMemberAttribute()]
        public global::System.Int32 Quantity
        {
            get
            {
                return _Quantity;
            }
            set
            {
                OnQuantityChanging(value);
                ReportPropertyChanging("Quantity");
                _Quantity = StructuralObject.SetValidValue(value);
                ReportPropertyChanged("Quantity");
                OnQuantityChanged();
            }
        }
        private global::System.Int32 _Quantity;
        partial void OnQuantityChanging(global::System.Int32 value);
        partial void OnQuantityChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public Nullable<global::System.Decimal> UnitPrice
        {
            get
            {
                return _UnitPrice;
            }
            set
            {
                OnUnitPriceChanging(value);
                ReportPropertyChanging("UnitPrice");
                _UnitPrice = StructuralObject.SetValidValue(value);
                ReportPropertyChanged("UnitPrice");
                OnUnitPriceChanged();
            }
        }
        private Nullable<global::System.Decimal> _UnitPrice;
        partial void OnUnitPriceChanging(Nullable<global::System.Decimal> value);
        partial void OnUnitPriceChanged();

        #endregion

    
        #region Navigation Properties
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [XmlIgnoreAttribute()]
        [SoapIgnoreAttribute()]
        [DataMemberAttribute()]
        [EdmRelationshipNavigationPropertyAttribute("OrdersModel", "FK_OrderDetails_Items", "Items")]
        public Item Item
        {
            get
            {
                return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedReference<Item>("OrdersModel.FK_OrderDetails_Items", "Items").Value;
            }
            set
            {
                ((IEntityWithRelationships)this).RelationshipManager.GetRelatedReference<Item>("OrdersModel.FK_OrderDetails_Items", "Items").Value = value;
            }
        }
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [BrowsableAttribute(false)]
        [DataMemberAttribute()]
        public EntityReference<Item> ItemReference
        {
            get
            {
                return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedReference<Item>("OrdersModel.FK_OrderDetails_Items", "Items");
            }
            set
            {
                if ((value != null))
                {
                    ((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedReference<Item>("OrdersModel.FK_OrderDetails_Items", "Items", value);
                }
            }
        }
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [XmlIgnoreAttribute()]
        [SoapIgnoreAttribute()]
        [DataMemberAttribute()]
        [EdmRelationshipNavigationPropertyAttribute("OrdersModel", "FK_OrderDetails_Orders", "Orders")]
        public Order Order
        {
            get
            {
                return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedReference<Order>("OrdersModel.FK_OrderDetails_Orders", "Orders").Value;
            }
            set
            {
                ((IEntityWithRelationships)this).RelationshipManager.GetRelatedReference<Order>("OrdersModel.FK_OrderDetails_Orders", "Orders").Value = value;
            }
        }
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [BrowsableAttribute(false)]
        [DataMemberAttribute()]
        public EntityReference<Order> OrderReference
        {
            get
            {
                return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedReference<Order>("OrdersModel.FK_OrderDetails_Orders", "Orders");
            }
            set
            {
                if ((value != null))
                {
                    ((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedReference<Order>("OrdersModel.FK_OrderDetails_Orders", "Orders", value);
                }
            }
        }

        #endregion

    }
    
    /// <summary>
    /// No Metadata Documentation available.
    /// </summary>
    [EdmEntityTypeAttribute(NamespaceName="OrdersModel", Name="SalesPerson")]
    [Serializable()]
    [DataContractAttribute(IsReference=true)]
    public partial class SalesPerson : EntityObject
    {
        #region Factory Method
    
        /// <summary>
        /// Create a new SalesPerson object.
        /// </summary>
        /// <param name="salesPersonID">Initial value of the SalesPersonID property.</param>
        public static SalesPerson CreateSalesPerson(global::System.Int32 salesPersonID)
        {
            SalesPerson salesPerson = new SalesPerson();
            salesPerson.SalesPersonID = salesPersonID;
            return salesPerson;
        }

        #endregion

        #region Primitive Properties
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=true, IsNullable=false)]
        [DataMemberAttribute()]
        public global::System.Int32 SalesPersonID
        {
            get
            {
                return _SalesPersonID;
            }
            set
            {
                if (_SalesPersonID != value)
                {
                    OnSalesPersonIDChanging(value);
                    ReportPropertyChanging("SalesPersonID");
                    _SalesPersonID = StructuralObject.SetValidValue(value);
                    ReportPropertyChanged("SalesPersonID");
                    OnSalesPersonIDChanged();
                }
            }
        }
        private global::System.Int32 _SalesPersonID;
        partial void OnSalesPersonIDChanging(global::System.Int32 value);
        partial void OnSalesPersonIDChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public global::System.String FirstName
        {
            get
            {
                return _FirstName;
            }
            set
            {
                OnFirstNameChanging(value);
                ReportPropertyChanging("FirstName");
                _FirstName = StructuralObject.SetValidValue(value, true);
                ReportPropertyChanged("FirstName");
                OnFirstNameChanged();
            }
        }
        private global::System.String _FirstName;
        partial void OnFirstNameChanging(global::System.String value);
        partial void OnFirstNameChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public global::System.String LastName
        {
            get
            {
                return _LastName;
            }
            set
            {
                OnLastNameChanging(value);
                ReportPropertyChanging("LastName");
                _LastName = StructuralObject.SetValidValue(value, true);
                ReportPropertyChanged("LastName");
                OnLastNameChanged();
            }
        }
        private global::System.String _LastName;
        partial void OnLastNameChanging(global::System.String value);
        partial void OnLastNameChanged();

        #endregion

    
        #region Navigation Properties
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [XmlIgnoreAttribute()]
        [SoapIgnoreAttribute()]
        [DataMemberAttribute()]
        [EdmRelationshipNavigationPropertyAttribute("OrdersModel", "FK_Orders_SalesPerson", "Orders")]
        public EntityCollection<Order> Orders
        {
            get
            {
                return ((IEntityWithRelationships)this).RelationshipManager.GetRelatedCollection<Order>("OrdersModel.FK_Orders_SalesPerson", "Orders");
            }
            set
            {
                if ((value != null))
                {
                    ((IEntityWithRelationships)this).RelationshipManager.InitializeRelatedCollection<Order>("OrdersModel.FK_Orders_SalesPerson", "Orders", value);
                }
            }
        }

        #endregion

    }
    
    /// <summary>
    /// No Metadata Documentation available.
    /// </summary>
    [EdmEntityTypeAttribute(NamespaceName="OrdersModel", Name="sysdiagram")]
    [Serializable()]
    [DataContractAttribute(IsReference=true)]
    public partial class sysdiagram : EntityObject
    {
        #region Factory Method
    
        /// <summary>
        /// Create a new sysdiagram object.
        /// </summary>
        /// <param name="name">Initial value of the name property.</param>
        /// <param name="principal_id">Initial value of the principal_id property.</param>
        /// <param name="diagram_id">Initial value of the diagram_id property.</param>
        public static sysdiagram Createsysdiagram(global::System.String name, global::System.Int32 principal_id, global::System.Int32 diagram_id)
        {
            sysdiagram sysdiagram = new sysdiagram();
            sysdiagram.name = name;
            sysdiagram.principal_id = principal_id;
            sysdiagram.diagram_id = diagram_id;
            return sysdiagram;
        }

        #endregion

        #region Primitive Properties
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
        [DataMemberAttribute()]
        public global::System.String name
        {
            get
            {
                return _name;
            }
            set
            {
                OnnameChanging(value);
                ReportPropertyChanging("name");
                _name = StructuralObject.SetValidValue(value, false);
                ReportPropertyChanged("name");
                OnnameChanged();
            }
        }
        private global::System.String _name;
        partial void OnnameChanging(global::System.String value);
        partial void OnnameChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
        [DataMemberAttribute()]
        public global::System.Int32 principal_id
        {
            get
            {
                return _principal_id;
            }
            set
            {
                Onprincipal_idChanging(value);
                ReportPropertyChanging("principal_id");
                _principal_id = StructuralObject.SetValidValue(value);
                ReportPropertyChanged("principal_id");
                Onprincipal_idChanged();
            }
        }
        private global::System.Int32 _principal_id;
        partial void Onprincipal_idChanging(global::System.Int32 value);
        partial void Onprincipal_idChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=true, IsNullable=false)]
        [DataMemberAttribute()]
        public global::System.Int32 diagram_id
        {
            get
            {
                return _diagram_id;
            }
            set
            {
                if (_diagram_id != value)
                {
                    Ondiagram_idChanging(value);
                    ReportPropertyChanging("diagram_id");
                    _diagram_id = StructuralObject.SetValidValue(value);
                    ReportPropertyChanged("diagram_id");
                    Ondiagram_idChanged();
                }
            }
        }
        private global::System.Int32 _diagram_id;
        partial void Ondiagram_idChanging(global::System.Int32 value);
        partial void Ondiagram_idChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public Nullable<global::System.Int32> version
        {
            get
            {
                return _version;
            }
            set
            {
                OnversionChanging(value);
                ReportPropertyChanging("version");
                _version = StructuralObject.SetValidValue(value);
                ReportPropertyChanged("version");
                OnversionChanged();
            }
        }
        private Nullable<global::System.Int32> _version;
        partial void OnversionChanging(Nullable<global::System.Int32> value);
        partial void OnversionChanged();
    
        /// <summary>
        /// No Metadata Documentation available.
        /// </summary>
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public global::System.Byte[] definition
        {
            get
            {
                return StructuralObject.GetValidValue(_definition);
            }
            set
            {
                OndefinitionChanging(value);
                ReportPropertyChanging("definition");
                _definition = StructuralObject.SetValidValue(value, true);
                ReportPropertyChanged("definition");
                OndefinitionChanged();
            }
        }
        private global::System.Byte[] _definition;
        partial void OndefinitionChanging(global::System.Byte[] value);
        partial void OndefinitionChanged();

        #endregion

    
    }

    #endregion

    
}

This is lot of code, but the best thing is that you don't have to write any of it. Entity Framework automatically generates code, adding methods to add  / update data in your database. Your business logic will work with this code rather than the database itself.

In the next post we will use this code to develop a functional project.

Thank you.

Entity Framework - An Overview

In previous post we briefly touched upon entity framework. Today, we will start a multi-part series on Entity Framework. In this series we will start with an overview about entity framework, then walk through creating a sample project using entity framework. We will then move on to using LINQ and Lambda Expressions.

Consider a Sales Order System with your standard normalized database. Your database schema will look something like this...

While this is a simple subset of a normalized database, imagine if you want to know which sales person sold most number of items and to which customer? You will have to write SQL query similar to this...

SELECT C.FirstName,C.LastName,Count(O.OrderID)As TotalOrders,S.FirstName,S.LastName
FROM Customers C INNER JOIN Orders O ON C.CustomerID=O.CustomerID
INNER JOIN SalesPerson S ON O.SalesPersonID=S.SalesPersonID
Group By C.FirstName,C.LastName,S.FirstName,S.LastName

This is fairly complex query for what you want to accomplish. Add the order dates or the total items per order and you can imagine how complex it will get. What if we create a view that groups this information together and you can then write a query against this view.

Although views are logical abstractions and can abstract many of the data mapping complexities, views are limited in what they can do. Further, if your database is being used for multiple applications, you may be creating multiple views and database management may become an arduous task.

Entity framework allows for abstracting the data mapping to create logical schema commonly called entity data mapping (EDM). Entities also allow for inheritance and hierarchy, giving you object relationship hierarchy as well as abstracting some of the complexities. The underlying relationships between the tables and attributes are managed in XML files, but you don't have to know the intricate details of the XML layout and schema. The logical objects will have to be mapped to the actual tables in the database obviously, but a developer doesn't have to worry about these details; framework takes care of the mappings between the logical abstraction layer and the underlying database. As far a business logic is concerned, it works with the entity data model as opposed to the database.

Entity Framework also provides facility to insert and update data without having to write an insert and update SQL queries. Further, the data is persisted in memory and any changes are tracked to ensure only changed records are updated.

Since most developers are familiar with SQL scripts, entity framework provides a very similar language to SQL that allows you to work with the EDM just as you would with database tables.

For example, if you created an EDM that wraps all the information that you need (as defined in the above query) in one object called "SalesOrderHistory" you can run a query against this object in the following fashion...

SELECT SO.CustomerName,SO.SalesPerson,Count(SO.OrderID) FROM
SalesOrderHistory AS SO Group By SO.CustomerName, SO.SalesPerson

This is very similar to SQL, but you must use alias for the entity names.

In subsequent posts we will dig deeper in this topic and see an example in action, so be sure to check back soon.

Thank you.

Object Relational Mapping - An Introduction

Most business applications have n-tier architecture - a presentation layer, business layer and data layer. As we discussed in previous post, datasets, datatables, and data adapters provide a mechanism to fetch and insert/update data into your data store.

Typed Datasets provide another layer of abstraction where you can separate data layer from business layer. Typed Datasets can represent database schema and provide strongly typed data elements, referential integrity and data constraints.

But typed datasets are limited and any changes in database schema necessitates changing one or more datasets that reference the tables/views etc.

Several free tools have been developed and refined over the years to automatically generate data layer, providing ways to retrieve data from the database, building objects, persisting them and then updating the data in the database. This family of tools is generally called Object Relational Mapping Tools or simply Code Generators. Some ORM tools are obviously more powerful and easy to use than others, but they work by analyzing your database schema and generating classes that are representative of your database tables, view, stored procedures or user defined functions. In essence, these tools build a collection of classes with each column represented as a property.

Maintaining and updating data layer becomes very easy. Whenever you make changes to the database schema, you can easily drop the entire data layer, run the tool again to regenerate the data layer in just few minutes.

ORM tools generate one class per table and create a relationship between multiple classes by creating reference variables similar to the database relationships. There are times where you may want to make custom changes in the generated classes, such as adding a new method or a property. You can easily do so by creating partial classes with the same name. You don't want to modify same classes that are generated by the tool because you would drop and recreate them as your schema changes.

There are several tools out there and I will name a few here that you can review, evaluate and use in your own work. Below is a list of tools that I have used over the years. This is not an exhaustive list and there are possibly some more/better tools out there.

Few ORM Tools

• nHibernate
• Subsonic
• OPF.Net
• OpenAccess ORM
• Wilson O/R Mapper
• Microsoft ADO.NET Entity Framework (Available with Framework 3.5 and up)

Most of these tools are quite powerful and easy to use. Some are freeware while others are licensed. Since our focus in this blog is Microsoft and .NET Framework, we will review Entity Framework in more detail in subsequent posts.

Thank you.

Typed vs. Untyped Datasets

In last few posts we discussed how you can use data adapter, datatables and datasets to fetch data from and save it to SQL Server. In those examples, we created untyped datasets. Untyped dataset is simply an in memory declaration of a dataset. It is a collection of datatables, which themselves are in memory declaration of datatables which are similar to database tables.

While it is possible to create constraints such as column type, relationship between columns, whether a column is nullable or not in a datatable declared in memory, but you have to write extra code for it. Contrast that with a typed dataset which inherits from dataset class and has an XML schema. This allows for you to define constraints and relationships on the design surface. Additionally, you can also drag tables from the database on the design surface and relationships/constraints of your database will automatically follow.

In this example, we will create a dataset using AdventureWorks database and then dragging the tables on the design surface. I am using VS 2010 for this project.

1. Create a sample test project.
2. From the top menu select Data > Add New Data Source and connect to the database you would like to use.

3. Select database and click on Next

4. Create a New Connection or select an existing connection. Here we will create a new connection

5. Define parameters and select your database.

6. Select one or more tables/views/stored procedures/function you would like to include in this dataset. Here we will select all tables.

7. Click on Finish and it will create an xsd file with all the selected tables.

8. If you open the file, you will see all tables that are part of this xsd

Alternatively, you can also create an xsd file manually and drag the tables that you want on the design surface. Click on Add new file and then select an xsd file. Then drag the tables to the design surface. As you drag the tables, note the data type and relationship between the tables.

In addition to dragging the tables on the design surface, you can also add table(s) by right clicking on the design surface and then clicking on Add > New Table or create a new relationship constraint.

Using Typed Dataset

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace TypedDataSets
{
    class Program
    {
        static void Main(string[] args)
        {
            Customers customer1 = new Customers();
            //populate the dataset
            // Step 2: Specify a command (request)
            string connectionstring = System.Configuration.ConfigurationManager.
            ConnectionStrings["TypedDataSets.Properties.Settings.
            AdventureWorksConnectionString"].ToString();
            SqlConnection myConnection = new SqlConnection(connectionstring);
            myConnection.Open(); 
            SqlCommand myCommand = myConnection.CreateCommand();

            myCommand.CommandType = CommandType.Text;
            myCommand.CommandText = "SELECT TOP 10 CustomerID,TerritoryID,AccountNumber, " + 
                "CustomerType,rowguid,ModifiedDate FROM Sales.Customer";

            // now use data sets instead of data read
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = myCommand;
            da.Fill(customer1.Customer);

            Console.WriteLine("Customer ID: " + customer1.Customer[0].CustomerID);
            Console.Read();
        }
    }
}

Typed datasets allow for strong typing and allows for cleaner code, in the sense you don't have to convert data from one type to another or check for the data type before using it.

Thank you.

Using ADO.NET - Part IV

In previous post we discussed how you can use dataset to insert new row and then use HasChanges() method and GetChanges() to only get newly added rows and insert them in the database instead of looping through all rows.

Today, we will see how you can use SQL Data Adapter to update the changes made to the data table and also Datarow's rowstate property to detect a deleted row and then delete it in the database.

In the first example, I have created a method that will change a datarow and then use dataadapter to update the changed row. I didn't have to loop through all records to determine which rows were updated.

Updating Modified Rows

private static void UpdateCustomer(DataSet ds, SqlConnection myConnection)
        {
            SqlCommand myCommand = myConnection.CreateCommand();
            
            //lets modify a record
            if (ds.Tables["Customers"].Rows.Count > 0)
            {
                ds.Tables["Customers"].Rows[0]["TerritoryID"] = "7";
                ds.Tables["Customers"].Rows[0]["CustomerType"] = "S";
                ds.Tables["Customers"].Rows[0]["ModifiedDate"] = DateTime.Now;

            }

            //now save this data in database. 

            //create a command object. 
            string strSQL = "sp_UpdateCustomer";
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.Add(new SqlParameter("@CustomerID", 
                          SqlDbType.Int,4,"CustomerID"));
            myCommand.Parameters.Add(new SqlParameter("@TerritoryID", 
                          SqlDbType.Int,4,"TerritoryID"));
            myCommand.Parameters.Add(new SqlParameter("@CustomerType", 
                          SqlDbType.Char,1,"CustomerType"));
            myCommand.Parameters.Add(new SqlParameter("@ModifiedDate", 
                          SqlDbType.DateTime,8,"ModifiedDate"));

           
            myCommand.CommandText = strSQL;
            SqlDataAdapter da = new SqlDataAdapter();
            da.UpdateCommand = myCommand;

            da.Update(ds.Tables["Customers"].GetChanges());

        }

In the second example, we will delete a row in a data table then detect the deleted row to delete it from the database.

Deleting a Deleted Row

 private static void DeleteCustomer(DataSet ds, SqlConnection myConnection)
        {
            SqlCommand myCommand = myConnection.CreateCommand();
            string strSQL = "sp_DeleteCustomer";
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.CommandText = strSQL;
            myCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int));

            //lets modify a record
            if (ds.Tables["Customers"].Rows.Count > 0)
            {
                ds.Tables["Customers"].Rows[0].Delete();   //delete a record

            }
            foreach (DataRow CustomerRow in ds.Tables["Customers"].GetChanges().Rows)
            {
                //use row state to determine the deleted row
                switch (CustomerRow.RowState)  
                {
                    case DataRowState.Deleted:
                        //use original version to get the ID
                        myCommand.Parameters["@CustomerID"].Value= 
                        CustomerRow["CustomerID",DataRowVersion.Original];   
                        myCommand.ExecuteNonQuery();
                        break;
                 }
                          
            }
                       

        }

Calling from the Main method

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ADONET2ConnectedCompact
{
    class Program
    {
        static void Main(string[] args)
        {
            // Step 1: Handshake with the database
            string connectionstring = System.Configuration.ConfigurationManager.
                                      ConnectionStrings["MyDBConnection"].ToString();
            SqlConnection myConnection = new SqlConnection(connectionstring);
           myConnection.Open();
           try
           {
               //DemonstrateRowState();
               //Console.Read();

                   // Step 2: Specify a command (request)
               SqlCommand myCommand = myConnection.CreateCommand();

               myCommand.CommandType = CommandType.Text;
               myCommand.CommandText = "SELECT TOP 10 CustomerID,TerritoryID,AccountNumber,
                                       CustomerType,ModifiedDate FROM Sales.Customer";
              
               // now use data sets instead of data read
               SqlDataAdapter da = new SqlDataAdapter();
               da.SelectCommand = myCommand;
               DataSet ds = new DataSet();
               da.Fill(ds);

               // Rename the table in dataset
               ds.Tables[0].TableName = "Customers";
               
               //Dataset is a disconnected representation of the 
               //data in memory. You can update/delete/insert data into this dataset 
               //and then save to the database.
               
               //Update Example
               UpdateCustomer(ds, myConnection);
               //Delete Example
               DeleteCustomer(ds, myConnection);
           }
           catch (Exception ex)
           {
               Console.WriteLine("Exception{0}: ", ex.Message);
           }
           finally
           {
               myConnection.Close();
           }
            

        }

As you saw in last few posts, dataset allows you to store data in memory, move it around, insert a new row, update or delete an existing row and then upload changes to the database.

So far we concerned ourselves with untyped datasets. In the next post we will review typed datasets and the differences between the two.

Thank you.