none
How to use objects with Linq RRS feed

  • Question

  • I dragged a table onto the designer after adding a Linq to SQL file. For brevity the table is called customers and has CustomerId, Name and Age.

    How could i use a Customer object i.e. a class that has properties mimicing the fields in the table that could be used with an N Tier app?

    Thanks

    Wednesday, December 12, 2012 8:55 PM

Answers

  • If you used the designer to drag a table called customer, you will have created for you a customer class.  This class comes with the ability to use the table columns as properties in the customer class.  Along with that it will have created a datacontext class.  If you look at your project tree you will see the dataclass1 or whatever you named it when you invoked the designer.  It is in the .designer class that you will find all the information about the data classes that correspond to your tables.

    One thing to note is that Linq to SQL will not create the methods need to create/update/remove an item from the database unless it has a primary key.

    A simple senario for you to create a customer row: (make sure you import/using the DataContext namespace)

    1.  create an instance of the DataContext class

    2.  create an instance of the customer class

    3.  update the Name property and the customerId property only if it is not a database generated key (identity in SQL Server)

    Now we see the magic

    4.  there is a readonly customers property on the DataContext that gives you access to the methods need to manipulate the customer class.  For an insert use the InsertOnSubmit(your customer class instance) method.  At this point nothing has been sent to the database.

    5.  when you want to commit this instance of the customer to the database use the SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict) method to send the info to the database, if the PK is an identity the PK for the row will be returned.

    For a change things are about as simple:  (always have an active DataContext object to use Linq)

    1.  using a Linq query return an instance of a customer row (or a collection if you like)

    2. make changes to the properties

    3. use the SubmitChanges as above

    For Remove:

    1.  using a Linq query return an instance of a customer row (or a collection if you like)

    2.  use the DeleteOnSubmit method of the customers property of the DataContext

    3. use the SubmitChanges as above

    Last thing

    The DataContext class has a GetChangeSet method that will give you the counts of pending (not sent) inserts/update/deletes.

    The customers property will have GetOriginalEntityState method which allows you to selectively reset any changes you have made to the customer record.

    Hope this helps

    Lloyd Sheen

     


    Lloyd Sheen

    • Marked as answer by Pure Deal Friday, December 14, 2012 3:17 PM
    Wednesday, December 12, 2012 11:08 PM

All replies

  • Take a look at begginers guide of LINQ to SQL

    http://www.youtube.com/watch?v=zy4Y34brSC8


    Please Mark as Reply and Vote as Helpful if I helped.

    Also please visit my blog http://msguy.net/

    Wednesday, December 12, 2012 9:10 PM
  • Thanks for the link. That's not exactly what I was after. It seems the video shows the tables in the dbml file but I didn't see any business logic created to separate it. In other words I think he didn't create an object where he could pass the values around unless I've misunderstood something?

    Thanks

    Wednesday, December 12, 2012 9:38 PM
  • Can you please be more  specific, what do you want to do with your customer table that is dropped to LINQ designer?

    Please Mark as Reply and Vote as Helpful if I helped.

    Also please visit my blog http://msguy.net/

    Wednesday, December 12, 2012 9:46 PM
  • Yes sure. Some background in the way I've done things in the past: create an access layer first this talks to the database and does the usual CRUD (Create, read, update delete). I then usually have a Business layer class where I do something like:

    Public sub UpdateCustomer (byref cust as Customer)

    ' some code that calls the access layers update method

    End sub

    Public class Customer

    public property customerId

    public property Name

    etc

    End class

    So im trying to see how I would do the same in linq and have separate layers.

    Is that any clearer?

    Thanks

    Wednesday, December 12, 2012 10:01 PM
  • If you used the designer to drag a table called customer, you will have created for you a customer class.  This class comes with the ability to use the table columns as properties in the customer class.  Along with that it will have created a datacontext class.  If you look at your project tree you will see the dataclass1 or whatever you named it when you invoked the designer.  It is in the .designer class that you will find all the information about the data classes that correspond to your tables.

    One thing to note is that Linq to SQL will not create the methods need to create/update/remove an item from the database unless it has a primary key.

    A simple senario for you to create a customer row: (make sure you import/using the DataContext namespace)

    1.  create an instance of the DataContext class

    2.  create an instance of the customer class

    3.  update the Name property and the customerId property only if it is not a database generated key (identity in SQL Server)

    Now we see the magic

    4.  there is a readonly customers property on the DataContext that gives you access to the methods need to manipulate the customer class.  For an insert use the InsertOnSubmit(your customer class instance) method.  At this point nothing has been sent to the database.

    5.  when you want to commit this instance of the customer to the database use the SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict) method to send the info to the database, if the PK is an identity the PK for the row will be returned.

    For a change things are about as simple:  (always have an active DataContext object to use Linq)

    1.  using a Linq query return an instance of a customer row (or a collection if you like)

    2. make changes to the properties

    3. use the SubmitChanges as above

    For Remove:

    1.  using a Linq query return an instance of a customer row (or a collection if you like)

    2.  use the DeleteOnSubmit method of the customers property of the DataContext

    3. use the SubmitChanges as above

    Last thing

    The DataContext class has a GetChangeSet method that will give you the counts of pending (not sent) inserts/update/deletes.

    The customers property will have GetOriginalEntityState method which allows you to selectively reset any changes you have made to the customer record.

    Hope this helps

    Lloyd Sheen

     


    Lloyd Sheen

    • Marked as answer by Pure Deal Friday, December 14, 2012 3:17 PM
    Wednesday, December 12, 2012 11:08 PM
  • In short ... you don't need, or really has a use for (IMO), your UpdateCustomer() method because LINQ gives you the context.SubmitChanges() method.  Also, remember that you can customize your designer objects because LINQ creates partial classes, so you can do some customizations in a partial class .cs file.

    Brent Spaulding | Access MVP

    Wednesday, December 12, 2012 11:48 PM
  • I'm doing something like this every time.

    1. I drop all of my database objects into DBML
    2. I'm creating classes that do only transport job. Take parameters from the interface layer and pass them to DB or viceversa.

    For Example lets consider CustomerService

    namespace CustomerService
    {
        public class Customer
        {
            public int ID { set; get; }
            public string FName { set; get; }
            public string LName { set; get; }
    
            public void UpdateCustomer(int ID, string FName, string LName)
            {
                NWDataContext db = new NWDataContext();
                var entity = db.Customers.Where(c => c.CustomerID == ID).FirstOrDefault();
                if (entity != null)
                {
                    entity.FirstName = FName;
                    entity.LastName = LName;
                    db.SubmitChanges();
                }
            }
        }
    }


    Please Mark as Reply and Vote as Helpful if I helped.

    Also please visit my blog http://msguy.net/

    Thursday, December 13, 2012 8:32 AM