locked
Linq to SQL RRS feed

  • Question

  • I develop web applications with 3-tiers patterns. I use custom business entities to manage data, and I write a custom DAL to serialize to/deserialize from SQL database.

    I'l like to use Linq to SQL to create business entities, and to have a DAL "ready to go" after I created DB schema. But I cannot find a good way to implement a 3-tiers web application using Linq to SQL instead of custom entities/DAL.

    Any suggestion?

    thanks

    Sunday, February 17, 2008 4:48 PM

Answers

  •  

    Baz,

     

    Here is the help for LINQ to SQL, in case you haven't read it:

     

    http://msdn2.microsoft.com/en-us/library/bb425822.aspx#linqtosql_topic4

     

    In answer to your question, and taken from the above document, the association between the foreign key id and the object mapping, such as an object containing a collection of objects could be explained by the following association code:

     

    Code Snippet
    [Table(Name="Customers")]
    public class Customer
    {
       [Column(Id=true)]
       public string CustomerID;
       ...
       private EntitySet<Order> _Orders;
       [Association(Storage="_Orders", OtherKey="CustomerID")]
       public EntitySet<Order> Orders {
          get { return this._Orders; }
          set { this._Orders.Assign(value); }
       }
    }

     

     

    As you can see the foreign key CustomerID is used to join the order and customers tables, and translate them to a collection of orders owned by the customer object.

     

    The DataContext object is the link (with a 'k') between the database structure and the entities, and their mappings.  I would probably refer to this as the data layer that you were talking about. 

     

    The LINQ commands, as you quite rightly point out would be considered to be more in the business layer, although I think that the lines are blurred, which does make things confusing.

     

    I hope this helps,

     

    Martin Platt.

     

    Monday, February 25, 2008 2:44 AM
  • Hi,

     

    I always follow standard ADO.net and layering my project. For LINQ, I used it to have quick query or use of data directly to my presentation layer.

     

    What I do is:

    1. Create new .dbml file in presentation layer. I often name it as LINQMyTableName

    2. I drag the table i need to the .dbml file. Now i know LINQ had provided me a connection to db and created me my entities.

    3. In the code behind of my form/s i try to instantiate My LINQ. Note: LINQ will provide you a data context.

        Example :

        LINQMyTableNameDataContext db = new LINQMyTableNameDataContext ();

      

        you can go like this:

        var linq = from t in db.MyTableName where t.tableField == parameter select t;

     

        OR

     

        IQueryable<MyTableName> lnq = from t in db.MyTableName where t.tableField == parameter select t;

     

        to get the collection you need to do foreach loop:

        foreach(MyTableName table in lnq)

        {          

              string val1 = table.field1;

              string val2 = table.field2;

              ...... continue or iteration...

        }

     

    Happy Coding.

     

     

     

    Thursday, March 13, 2008 8:16 AM

All replies

  • Hi baz,

     

    I think what you might want to look at is the Entity Framework.

     

    This article also gives you some information on how all that works.

     

    The idea being that you abstract the SQL implementation away from the program by using LINQ.  I would assume that you already knew that though, and that's why you chose it, right?

     

    Hope that helps you,

     

    Martin Platt.

     

    Monday, February 18, 2008 4:47 AM
  • Hi Martin,
    I don't know if entity framework is a requirenment for my goal, beacause my applications have a one-to-one mapping from tables to business entities. Are there other features EF has over Linq To Sql that I may consider?

    I know basic of Linq to Sql and I tested simple samples as thoose that Scott's post shows, but I've not sure about how to use Linq To Sql in a real world three tiers application. For example, how to implement an update function in a CRUD pattern: how bll funcion may receive data from caller, how may use Linq to update (Scott's post retrieve a record from DB with a read before to update it: it seems an expensive approach), and so on...
    More important: if I have a table that reference to an other table, VS Designer creates two types and the first has a properties that links to the second. But, checks this sample:

    Class Order (ID as Int32, date, etc..)
    Class OrderRow (ID, OrderID as Int32, description, etc.).

    The designer will create a class OrderRow with a property OrderID as Int32. And all subsequent operations will work with this property. But is this right? In my custom business model I create a class Order, a class OrderRow, and OrderRow will have a property Order as Order. This obvious, isn't it? But this will work in three tiers web applications as well in full OOP projects.
    With Linq to SQL and VS 2008 how is the right way to handle this scenarios?

    Maybe I'm not clear, but the question is very extended... Sad

    thanks


    Tuesday, February 19, 2008 9:39 PM
  • In terms of using the technology, probably I'd have to say that I would be giving bad advice if I was to try to answer, but there's a forum on here for ADO.NET for 2008, including LINQ : http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=533&SiteID=1

     

     

    The idea behind entity framework is that it helps map from a database structure to an entity structure, including hiding a lot of the specifics of the database type also.  LINQ to SQL is part of the entity framework.

     

    From what I understand you would use LINQ to specify a query, and then create the entity object given the result.  That shouldn't change whilst the real database operations and queries are mapped for you, and may be changed without the code changing?  Same goes for swapping the RDBMS to something different.

     

    From the Entity Framework information on the Microsoft site:

    Code Snippet
    // we'll use the order-tracking store
    using(OrderTracking orderTracking = new OrderTracking()) {
    
        // find all the pending orders for sales people
        // in Washington
        var orders = from order in orderTracking.SalesOrders
                     where order.Status == "Pending Stock Verification" &&
                           order.SalesPerson.State == "WA"
                     select order;
    
        foreach(SalesOrder order in orders) {
    
            // obtain a list of StockAppProduct objects
            // to be used for validation
            List<StockAppProduct> products = new List<StockAppProduct>(
                from orderLine in order.Lines
                select new StockAppProduct {
                    ProductID = orderLine.Product.ID,
                    LocatorCode = ComputeLocatorCode(orderLine.Product)
                }
            );
    
            // make sure all products for this order
            // are in stock through the stock management
            // system
            if(StockApp.CheckAvailability(products)) {
                
                // mark the order as "shippable"
                order.Status = "Shippable";
            }
        }
    
        // if we marked one or more orders as shippable, persist
        // the changes in the store
        orderTracking.SaveChanges();
    }
     

     

    The mapping between the database and the entity appears to be at the foreach loop. I would imagine that you'd use this piece of code in the data layer, to return entities.
    The key to this approach, using LINQ and entity framework is that there is a trade off between performance and abstraction.  You have to decide which is most important in your system.
    The way the designer creates your components, you could still perform the logic to map to and Order object, but what the designer has done is created an object with the real mappings that are held in the database.  And Order object only exists in memory within your application, and when you persist that object to the database you would store the order id.  From the ponit of view of OO it is correct to store the object, as that is the real relationship, but from the point of view of the technology used in a relational database, it is the table relationships that you consider instead.  
    You still need to consider the layers in the same way, I think, you just need to understand what level of abstraction using LINQ will give you over the standard way we have written data layers in the past.  It's similar, but the differences would be subtle.  The main benefit would seem to be maintainability.
    I hope this helps you.  I think that you need to read more about layers and abstraction, about what goes into the database layer, then perhaps the LINQ technology will make a little more sense to you.  Most of the questions are to do with RDBMS and OOP and the differences rather than LINQ.  It would worry me that you choose LINQ because it is new rather than because it is what your solution requires.
    I hope this helps give you some ideas.
    Martin Platt.
    Tuesday, February 19, 2008 11:19 PM
  • My goal is to use Linq To Sql to manage my data, that are business entities (objects) in my NET application and tables in my SQL DB. Without to write a lot a code (VB code, and either CRUD stored procedures) to:
    - create business objects that map db tables (I design DBs where tables have a 1-to-1 relationship with business objects)
    - deserialize data from db, to re-create object instances
    - serialize data to db, to save data my application (web pages) manage
    - update my db and business objects schemas when I upgrade my application requirenments

    I try to understand if this new technology permits to make this writing a lot few custom code. I know that this can have performance impact, but this is not my first problem.

    My actual reference is 3-tiers (5-tiers) design architecture: presentation, facade, business logic, dal, database. And, of course, business entities definition because I alwayse use custom object to handle data (I don't use datasets or other similar things).

    Your code snippet, for me, is not a DAL code. It's a BLL routine. In fact, it permits to remove my DAL code (the code where I call some stored procedure on db, retrieve data, create related business objects, return to BLL, and than save them back to db).
    Hope this can help to center the problem, that I think is a problem common to all developer and architects that write code with custom business objects and custome dal layers and want write less code.

    thanks


    Sunday, February 24, 2008 4:05 PM
  •  

    Baz,

     

    Here is the help for LINQ to SQL, in case you haven't read it:

     

    http://msdn2.microsoft.com/en-us/library/bb425822.aspx#linqtosql_topic4

     

    In answer to your question, and taken from the above document, the association between the foreign key id and the object mapping, such as an object containing a collection of objects could be explained by the following association code:

     

    Code Snippet
    [Table(Name="Customers")]
    public class Customer
    {
       [Column(Id=true)]
       public string CustomerID;
       ...
       private EntitySet<Order> _Orders;
       [Association(Storage="_Orders", OtherKey="CustomerID")]
       public EntitySet<Order> Orders {
          get { return this._Orders; }
          set { this._Orders.Assign(value); }
       }
    }

     

     

    As you can see the foreign key CustomerID is used to join the order and customers tables, and translate them to a collection of orders owned by the customer object.

     

    The DataContext object is the link (with a 'k') between the database structure and the entities, and their mappings.  I would probably refer to this as the data layer that you were talking about. 

     

    The LINQ commands, as you quite rightly point out would be considered to be more in the business layer, although I think that the lines are blurred, which does make things confusing.

     

    I hope this helps,

     

    Martin Platt.

     

    Monday, February 25, 2008 2:44 AM
  • Isn't that kind of the idea of the Entity Framework to hide away the DAL? The LINQ code may look like DAL but it really isn't, it's a query language sure, but one for manipulating data in models rather than actual stores so it doesn't break any puriest rules about DAL in BL (?).



    Wednesday, March 5, 2008 1:32 AM
  • With the coming of LINQ to SQL I really find it useful. I still follow my custom abstraction of the ADO.Net but in my application, I also include LINQ. What I did is i make LINQ as an immediate resource for my application. Like making it for query bunch of data result. Mostly my transaction are still passing thru my Data Layer which is in ADO.Net.  

    Wednesday, March 12, 2008 12:59 AM
  • I see Linq to SQL, Linq to Entities, Linq to Objects and Linq to XML all those are DAL, except the DAL itself is abstracted and commonized from the coding perspective...

     

    Business Layer, Business Process Layer, Workflow Layer all are nothing but Orchestration layer.

     

    WPF, WinForms are Presentation/User Experience Layer.

     

    WCF provides separation of layer without hardwiring the physical layers into logical structures, so scaling, high availability becomes easier and somewhat removed from application programmer's mind.

     

    Because we are using Linq, does not mean it becomes BL and DAL goes away.

     

    DAL will always be the abstraction of data model whether it is xml, database, external objects, etc., whether you are using linq or straight ado.net

    Wednesday, March 12, 2008 4:55 AM
  • Bermil, can you provide some short example of your way of use LINQ?

    thanks

    Wednesday, March 12, 2008 4:58 PM
  • Hi,

     

    I always follow standard ADO.net and layering my project. For LINQ, I used it to have quick query or use of data directly to my presentation layer.

     

    What I do is:

    1. Create new .dbml file in presentation layer. I often name it as LINQMyTableName

    2. I drag the table i need to the .dbml file. Now i know LINQ had provided me a connection to db and created me my entities.

    3. In the code behind of my form/s i try to instantiate My LINQ. Note: LINQ will provide you a data context.

        Example :

        LINQMyTableNameDataContext db = new LINQMyTableNameDataContext ();

      

        you can go like this:

        var linq = from t in db.MyTableName where t.tableField == parameter select t;

     

        OR

     

        IQueryable<MyTableName> lnq = from t in db.MyTableName where t.tableField == parameter select t;

     

        to get the collection you need to do foreach loop:

        foreach(MyTableName table in lnq)

        {          

              string val1 = table.field1;

              string val2 = table.field2;

              ...... continue or iteration...

        }

     

    Happy Coding.

     

     

     

    Thursday, March 13, 2008 8:16 AM