Using DLINQ with ASP.NET (Part 2 of my LINQ series)

 

Last month I wrote about the new LINQ language features for VB and C#.  My blog post introduced some of the core concepts of LINQ, and demonstrated how you can use LINQ to perform rich queries over standard .NET business classes and object collections.  I also showed how you could easily bind the results of these queries against ASP.NET controls.  You can read all about how to-do this here.

 

One of the really powerful aspects of LINQ is that it can be used against any type of object or data source, and provides a consistent programming model for doing this.  LINQ ships with implementations that support LINQ against regular object collections, databases, entities, and XML sources.  Because LINQ supports rich extensibility, developers can also easily integrate LINQ with other data sources and providers as well. 

 

In this blog post I’m going to provide a quick overview of some of the key LINQ for databases concepts (aka DLINQ), and then walk through a sample that shows how you can start to use LINQ against a database within an ASP.NET 2.0 application. You can download the LINQ May CTP from here and use the steps below with VS 2005 or the free Visual Web Developers Express edition to follow along (note: the LINQ May CTP runs in VS 2005 and does not touch any existing binaries).  You can also download the completed samples I build below from here.

 

Reviewing some DLINQ concepts

 

DLinq provides a run-time framework for managing relational data as objects. It does this by translating LINQ queries into SQL for execution by the database and then translates the tabular results back into objects you define. Your application can then manipulate these objects however you want to.  While you do this DLinq will track any changes you make to them, so that you can optionally submit changes back to the database (for update, insert, and delete operations).

 

DLINQ allows you to define the object model and class structure that best represents your application data.  For example, if you have a Customers database table with columns “CustomerId”, “City”, and “CustomerName”, you could optionally create a “Customer” class that represented it like so:

 

public class Customer

{

    public string CustomerId;

    public string City;

    public string CustomerName;

}

 

Note that you can use whatever naming pattern you want in your classes -- you aren't required to have the same names as in the database. 

 

DLINQ allows you to map classes to a database schema in two ways.  The first option is by adding attributes to the classes that indicate where and how they should be stored in the database (this is called “Attribute Based Mapping”).  Alternatively you can specify the mapping using an external XML file.  The external XML file enables the underlying database schema to be kept separate from the code, and even allows runtime schema mapping changes on the fly (meaning you do not need to recompile an assembly using DLINQ to modify the database storage schema being used).  Developers can choose whichever mapping approach works best for them and their projects. 

 

In addition to supporting single table mappings, it is also possible to easily define relationships between tables using DLINQ.  In a relational database this is typically modeled using foreign-keys referring to primary keys in other tables.  DLINQ allows developers to define “Association Relationships” between classes to express relationships.  This allows a developer, for example, to write “Customer.Orders” to reference the collection of orders for the customer instance.

 

Important: It is not necessary to manually define your table mappings or relationships.  The May CTP drop of DLINQ ships with both a command-line utility and a Visual Studio data designer to make defining these relationships and mappings simple (it will take us less than 30 seconds to define a complete DLINQ mapping for the Northwind database in our sample below).

 

Once data mappings and relationships are defined, developers can then easily write LINQ code to perform queries and updates against a database.  For example, the below code uses the SQL Northwind database to retrieve all customers from London, and then prints out the Customer Name, as well as a hierarchical sub-listing of each of the customer’s orders to a page:

 

Northwind db = new Northwind(connectionString);

 

// Query for all customers in london

IEnumerable<Customer> customers = from cust in db.Customers

                                  where cust.City == "London"

                                  select cust;

 

// Fetch each customer and output its name and order history

foreach (Customer cust in customers) {

 

    Response.Write ("Customer = " + cust.CustomerName);

 

    foreach (Order order in cust.Orders) {

        Response.Write("---- OrderID: " + order.OrderId);

    }

}

 

Note how the Customer and Order classes are strongly-typed, and how the Customer class has an “Orders” association relationship that uses the ForeignKey/PrimaryKey relationship between the Customers and Orders tables in the database.

 

We can also then easily write code to retrieve an individual Customer and simultaneously update both its ContactName and add a new Order for it in the system like so:

 

Northwind db = new Northwind(connectionString);

 

// Fetch a specific customer

Customer cust = db.Customers.Single(c => c.CustomerID == "ALFKI");

 

// Change the name of the contact

cust.ContactName = "ScottGu";

 

// Create and add a new Order to the customer's orders collection

Order order = new Order();

order.OrderDate = DateTime.Now;

order.ShipCity = "London";

 

cust.Orders.Add(order);

 

// Save all the changes to database

db.SubmitChanges();

 

We do not need to write any additional data access code or define any SQL statements for the above two samples to work.  Instead, I can program and work with these objects for my data access, and have DLINQ perform the underlying data statements for me.  The compiler will automatically perform syntax checking against my queries/code and will give me warnings/errors if I do things wrong (for example: mistype a column name, misuse a double as an integer, try to convert an inappropriate string to a date, etc).  I can use the debugger to inspect any value.  And with the next release of VS I will get full intellisense completion when writing these queries and operations.

 

DLINQ supports Stored Procedures, Views, and User-Defined Functions, and allows developers to drop-down and add custom SQL where necessary.  Developers can add additional, non-database based, properties and methods to data classes to extend their semantics, and can also add both property and entity validation/business rules to them as well (the partial class feature in VB and C# makes doing this clean and easy).  Transactions are also fully supported, including two-phase commit transactions where the DLINQ operations can be enlisted in a broader transaction with multiple databases or storage providers.

 

In short – there is a lot of cool stuff there.  There is a lot of documentation that comes with the May LINQ CTP that you can read to learn more. 

 

What I’m going to do below for the rest of this post is walk through a few step-by-step instructions on how to start using the May LINQ CTP with DLINQ in an ASP.NET app.

 

Step 1: Create an ASP.NET LINQ Project

 

To begin with, we’ll create a new LINQ-enabled ASP.NET web site in VS 2005 or the free Visual Web Developer Express tool.  To-do this, make sure the May CTP build of LINQ is installed, and then choose File->New Web Site and select the “LINQ ASP.NET Web Site” template:

 

 

We now have a project created that references the LINQ and DLINQ assemblies, and can use the C# or VB LINQ-enabled compilers.

 

Step 2: Create a DLINQ enabled object model for the Northwind database

 

We’ll use the “Northwind” database sample that comes with SQL Server for our LINQ/DLINQ code below. 

 

We’ll start by creating an object model that maps to the Northwind database.  We can do this one of three ways:

 

1)     Write this by hand

2)     Use the new VS DLINQ designer to graphically define the class organization and relationships

3)     Use the built-in “SQLMetal.exe” command-line utility to create it for us

 

For this sample walkthrough I’m going to use #3.  To create the mapping I simply need to type the below statements in a command-prompt to create this for the Northwind database on my local box:

 

    >> cd c:\Program Files\LINQ Preview\Bin

    >> sqlmetal /database:Northwind /pluralize /namespace:Northwind /code:Northwind.cs

 

SqlMetal will infer the appropriate classes from the database metadata, and by default add appropriate validation constraint checks (NOT NULL, Column Types and Size Limits, etc) to the object model.  Because I’ve specified the /pluralize parameter option, it will use some default naming logic conventions to create the property and table names – for example creating a “Customer” class that maps to the “Customers” table (obviously you can go in and override these if you don’t like the defaults). 

 

We could optionally also specify a /map:[filename] parameter to control whether the database mappings are stored using code attributes or in an XML metadata file.  We could also indicate that stored procedures, functions and views should be mapped via the /views, /sprocs, /functions parameter switches.

 

The result of the SQLMetal’s code generation will be saved within the “Northwind.cs” file we specified and scoped within the “Northwind” code namespace above.  We’ll then want to copy this file under the “/app_code” directory within our new ASP.NET project:

 

 

The last step we’ll then want to do is to add a “<connectionStrings>” section within the web.config of our application to configure our database connection information:

 

  <connectionStrings>

    <add name="Northwind"

         connectionString="Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"

         providerName="System.Data.SqlClient"/>

  </connectionStrings>

 

This will allow us to easily change the database deployment location later.  With ASP.NET 2.0 we can also now optionally encrypt the web.config values for secure deployment.  ASP.NET 2.0 also installs a built-in admin extension to the IIS admin tool that allows IT administrators easily manage the connection strings during and after deployment as well.

 

Once the above steps are done, we are ready to use our newly created Northwind DLINQ data layer anywhere within our application.

 

Step 3: Build a page that uses our DLINQ layer

 

To start off with, we’ll create a new page within our project called “Sample1.aspx”. 

 

Within the Sample1.aspx file we’ll add a GridView server control and explicitly define a few columns to display:

 

<%@ Page Language="C#" CodeFile="Sample1.aspx.cs" Inherits="Sample1" %>

 

<html>

<body>

    <form id="form1" runat="server">

   

    <h1>Northwind Customers</h1>

   

    <asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server">

       <Columns>

          <asp:BoundField HeaderText="Customer ID" DataField="CustomerID" />

          <asp:BoundField HeaderText="Name" DataField="CompanyName" />

          <asp:BoundField HeaderText="City" DataField="City" />

          <asp:BoundField HeaderText="State" DataField="Region" />

       </Columns>

    </asp:GridView>

   

    </form>

</body>

</html>

 

Within the Sample1.aspx.cs code-behind file we’ll add a “using System.Query” statement at the top of the file, and then add LINQ code within the Page_Load() event to retrieve all US customers from the Northwind database, sorted by CompanyName, and programmatically bind the results to the GridView control:

 

using System;

using System.Configuration;

using System.Web.UI;

using System.Query;

 

public partial class Sample1 : System.Web.UI.Page {

 

    protected void Page_Load() {

 

        string connectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

       

        Northwind.Northwind db = new Northwind.Northwind(connectionString);

 

        GridView1.DataSource = from customer in db.Customers

                               where customer.Country == "USA"

                               orderby customer.CompanyName

                               select customer;

 

        GridView1.DataBind();

    }

}

 

When we save the page and access it from a browser we’ll get this output:

 

 

Disclaimer: I will leave it as an exercise to the reader to write a nice CSS stylesheet to make it look pretty – but hopefully you get the point of how the functionality works. J

 

Step 4: Data Shaping and Projections

 

In the step above we queried the database for Customers and returned back a sequence of “Customer” objects.  If you look at the underlying SQL statement executed (just attach the SQL Profiler tool while you run the app to inspect every data access statement), you’ll notice that it is returning all of the columns from the Customer table – including the ones we aren’t displaying.  This makes sense given that our query above asked for a sequence of Customer objects, although it isn’t ideal for this particular page scenario since we only care about four of the Customer columns.  In cases where you have columns containing a lot of data (for example: blobs of images, or large text strings, etc) this can lead to performance issues since you might end up pulling back more data than you really want/need.

 

One of the nice things about LINQ and DLINQ is that we aren’t constrained to having to always return fully populated entity objects.  Instead we can shape and transform the data however we want, without having to drop-down to write SQL directly.  LINQ and DLINQ use the new “anonymous type” features in C# and VB to provide an elegant way to express this (to learn more about anonymous types in LINQ read my previous LINQ blog post).

 

For example, I could modify our query above like so to just fetch the 4 columns we are going to display like so:

 

GridView1.DataSource = from customer in db.Customers

                       where customer.Country == "USA"

                       orderby customer.CompanyName

                       select new {

                           CustomerID = customer.CustomerID,

                           CompanyName = customer.CompanyName,

                           City = customer.City,

                           Region = customer.Region

                       };

 

If you re-run the page now and look at the SQL Profiler, you’ll notice that DLINQ has automatically changed the SQL executed to a more optimized statement that only returns the 4 column values we need from the database:

 

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[City], [t0].[Region]

FROM [Customers] AS [t0]

WHERE [t0].[Country] = @p0

ORDER BY [t0].[CompanyName]',N'@p0 nvarchar(3)',@p0=N'USA'

 

We can also use this database shaping technique to run aggregate functions and add computed values to our result as well.  For example, I could modify the query above to also add the number of orders the customer has made, and the most recent order date by writing it like so:

 

GridView1.DataSource = from customer in db.Customers

                       where customer.Country == "USA"

                       orderby customer.CompanyName

                       select new {

                           CustomerID = customer.CustomerID,

                           CompanyName = customer.CompanyName,

                           City = customer.City,

                           Region = customer.Region,

                           NumOrders = customer.Orders.Count,

                           LastOrder = customer.Orders.Max(o => o.OrderDate)

                       };

 

Note that both of these new values are computed off of the rows in the Orders table specific to the referenced Customer (the Order table has a FK relationship to Customers that SQLMetal automatically detected and used to setup an association).  The above LINQ query will cause the below SQL statement to execute in the database:

 

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[City], [t0].[Region], (

    SELECT COUNT(*)

            FROM [Orders] AS [t1]

            WHERE [t1].[CustomerID] = [t0].[CustomerID]

    ) AS [NumOrders], (

    SELECT MAX([t2].[OrderDate])

            FROM [Orders] AS [t2]

            WHERE [t2].[CustomerID] = [t0].[CustomerID]

    ) AS [LastOrder]

FROM [Customers] AS [t0]

WHERE [t0].[Country] = @p0

ORDER BY [t0].[CompanyName]',N'@p0 nvarchar(3)',@p0=N'USA'

 

Notice how DLINQ automatically translated the count and Max date functions into the single SQL query to execute -- it did not need to pull down all of the records to compute it (making it very efficient).

 

And now when we run this page it will generate this html output:

 

 

Step 5: Hierarchical Binding

 

Data shaping isn’t limited to adding only scalar values to the anonymous classes we return.  We can also return additional sub-collections of objects as part of our LINQ query.  For example, we could modify our query like so to return a sub-collection of the most recent 5 orders for each customer (note how the Take() LINQ aggregate method can be used to return only 5 results from the database):

 

GridView1.DataSource = from customer in db.Customers

                       where customer.Country == "USA"

                       orderby customer.CompanyName

                       select new {

                           CustomerID = customer.CustomerID,

                           CompanyName = customer.CompanyName,

                           City = customer.City,

                           Region = customer.Region,

                           NumOrders = customer.Orders.Count,

                           LastOrder = customer.Orders.Max(o => o.OrderDate),

                           Orders = customer.Orders.OrderByDescending(o => o.OrderDate).Take(5)

                       };

 

This query returns a collection of anonymous objects with each instance containing 7 properties – one of which is a sub-collection of at most 5 Orders associated with the customer.  I can then update my GridView like below to hierarchically bind the Customer’s orders within a templated column of the Grid to generate a bulleted list of “Recent Orders”:

 

<asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server">

 

  <Columns>

 

     <asp:BoundField HeaderText="Customer ID" DataField="CustomerID" />

     <asp:BoundField HeaderText="Name" DataField="CompanyName" />

     <asp:BoundField HeaderText="City" DataField="City" />

     <asp:BoundField HeaderText="State" DataField="Region" />

     <asp:BoundField HeaderText="NumOrders" DataField="NumOrders" />

           

     <asp:TemplateField HeaderText="Recent Orders">

           

        <ItemTemplate>

                   

          <ul> 

             <asp:Repeater datasource='<%# Eval("Orders") %>' runat="server">                   

                <ItemTemplate>

                   <li>

                     <%# Eval("OrderID") %>

                     (<%# Eval("OrderDate", "{0:dd MMM yyyy}")%>)

                   </li>

                </ItemTemplate>

                       

             </asp:Repeater>               

          </ul>

               

       </ItemTemplate>

                                                      

    </asp:TemplateField>

           

  </Columns>

 

</asp:GridView>

 

Which will cause this page to be output like so:

 

 

DLINQ in the sample above was smart and optimized the data access to only hit the database twice – once to retrieve the top-level customer data, and then once to retrieve all of the needed orders for our particular customers (DLINQ then split this orders result and associated each order correctly with the appropriate customer).  This perf optimization avoided us having to hit the database separately to populate and display each individual customer order collection (note that we could also have alternatively expressed to DLINQ to lazy populate the Orders if we wanted this behavior instead).

 

Step 6: Enable Basic Pagination

 

The previous step is nice because we can now see the 5 most recent orders for each customer, but the downside is that it has expanded the html height quite a bit.  To make the listing a little cleaner we’ll go ahead and enable paging support on the GridView, set the pagesize to 3 rows per page, and handle the appropriate page event handler in our code-behind to re-bind the Grid as appropriate when users click on the new page index at the bottom of the grid.

 

For completeness, here is what the entire .aspx file looks like with the Gridview with hierarchical binding and paging enabled:

 

<%@ Page Language="C#" CodeFile="Sample4.aspx.cs" Inherits="Sample4" %>

 

<html>

<body>

    <form id="form1" runat="server">

   

    <h1>Northwind Customers</h1>

   

    <asp:GridView ID="GridView1" AllowPaging="true" PageSize="3"

                  AutoGenerateColumns="false" runat="server"

                  OnPageIndexChanging="GridView1_PageIndexChanging">

 

       <Columns>

 

          <asp:BoundField HeaderText="Customer ID" DataField="CustomerID" />

          <asp:BoundField HeaderText="Name" DataField="CompanyName" />

          <asp:BoundField HeaderText="City" DataField="City" />

          <asp:BoundField HeaderText="State" DataField="Region" />

          <asp:BoundField HeaderText="NumOrders" DataField="NumOrders" />

               

          <asp:TemplateField HeaderText="Recent Orders">

               

              <ItemTemplate>                       

                 <ul> 

                    <asp:Repeater datasource='<%# Eval("Orders") %>' runat="server">

                           

                       <ItemTemplate>

                          <li>

                             <a href="todo"><%# Eval("OrderID") %></a>

                             (<%# Eval("OrderDate", "{0:dd MMM yyyy}")%>)

                          </li>

                       </ItemTemplate>

                           

                    </asp:Repeater>               

                 </ul>

                   

              </ItemTemplate>

                                                           

         </asp:TemplateField>

               

      </Columns>

 

    </asp:GridView>

  

    </form>

</body>

</html>

 

And here is then the entire code-behind:

 

using System;

using System.Configuration;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Query;

 

public partial class Sample4 : System.Web.UI.Page {

 

    void BindData() {

 

        string connStr = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

       

        Northwind.Northwind db = new Northwind.Northwind(connStr);

 

        GridView1.DataSource = (from customer in db.Customers

                               where customer.Country == "USA"

                               orderby customer.CompanyName

                               select new {

                                  CustomerID = customer.CustomerID,

                                  CompanyName = customer.CompanyName,

                                  City = customer.City,

                                  Region = customer.Region,

                                  NumOrders = customer.Orders.Count,

                                  LastOrder = customer.Orders.Max(o => o.OrderDate),

                                  Orders = customer.Orders.OrderByDescending(o => o.OrderDate).Take(5)

                               }).ToList();

 

        GridView1.DataBind();

    }

 

    protected void Page_Load() {

 

        if (Page.IsPostBack == false)

            BindData();

    }

 

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e) {

 

        GridView1.PageIndex = e.NewPageIndex;

        BindData();

    }

}

 

And now I have a pageable GridView, showing both relational data and calculated aggregate data, both in a tabular and hierarchical way:

 

 

Summary

 

The combination of LINQ and DLINQ provide a really powerful way to-do data access.  As you’ve seen above, it takes the grunge work out of data access code, and leaves you with an elegant way to easily perform data operations.  You also get compiler type-checking, code intellisense and debug support for everything (along with a WYSIWYG designer for both DLINQ relationship mapping and obviously ASP.NET pages).  The combination of LINQ/DLINQ and the rich control event model in ASP.NET provides a really productive and clean way to quickly build data web apps that also scale really well. 

 

In my next few LINQ posts I’ll show how we will be able to build on top of the concepts I demonstrated above to easily add sorting, in-line editing, deleting, and selection support over our customer data – and also show how to easily Ajax enable it with Atlas.

 

Hope this helps,

 

Scott

 

P.S. You can download the LINQ May CTP from here and use it with both VS 2005 and the free Visual Web Developers Express edition.  You can also download the completed samples I built above here.

 

 

 

Published Sunday, June 4, 2006 11:14 PM by ScottGu

Comments

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Monday, June 5, 2006 3:32 PM by John Smith
Great information.
In using SqlMetal, I have to specify the server. e.g.
   server:.\sqlexpress

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Monday, June 5, 2006 4:50 PM by DR
I am assuming that is is basically acting like an inbuilt OR-Mapper for .NET Framework. Correct me if I am wrong. So basically in n-tier architecture, this is going to replace the data access block  to just another layer of function calls?

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Tuesday, June 6, 2006 2:40 AM by Poppyto
DLINQ is so cool but a lot of projects use stored procedures (easiest for upgrades & security ). Is there any chance that DLINQ will be implemented directly in SQL Server ?

I'm also thinking about inner/outter joins with DLINQ ? (No samples in this tutorial ?).

Great tutorial anyway :)

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Tuesday, June 6, 2006 10:24 AM by davidacoder
I don't fully understand the paging sample. Does the .ToList() function cause the execution of the query? I would assume that in the other samples the query is actually only executed when the DataGrid is enumerating the first time through the data, i.e. NOT in the line with the assignment to DataSource happens, right?

Your example loads all rows of the table into memory at that particular line of code? That would be pretty inefficient for paging, right? You would really only want to load the data needed for a particular page for each postback. Or am I missing something here?

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Tuesday, June 6, 2006 11:33 AM by ScottGu
Hi Poppyto,

DLINQ supports SPROCs and User Functions -- so you can use it against a database that has those.  

You can also use the new "join" keyword in DLINQ to achieve joins.  For example, consider the below SQL query that calls a table function called "ProductsCostingMoreThan():

SELECT p2.ProductName, p1.UnitPrice
FROM dbo.ProductsCostingMoreThan(80.50)
AS p1 INNER JOIN Products AS p2 ON p1.ProductID = p2.ProductID

You could write this in DLINQ as:

GridView1.DataSource =
    from p in db.ProductsCostingMoreThan(80.50m)
    join s in db.Products on p.ProductID equals s.ProductID
    select new {
                p.ProductID,
                s.UnitPrice
            };

Note the use of anonymous types to shape the data, and how everything is type-safe (providing compile-time checking and intellisense).

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Tuesday, June 6, 2006 11:39 AM by ScottGu
Hi David,

For my paging sample above I just did middle-tier paging, which isn't very efficient for large tables (as you noted above ToList() materializes the list of results).

My next DLINQ post is going to show off server side paging w/ column sorting in the Grid.  It is really easy to-do -- but introduces a new concept that I thought might be too much for the (already long) post above.  

One thing that is cool about DLINQ is that you can also use the Take() and Skip() operators on any sequence to limit and seek into the sequence results.  DLINQ actually uses these under the covers to write SQL that only fetches the needed rows from the database.  So for example this code:

  GridView1.DataSource = db.Customers.Seek(100).Take(10);

Would only return 10 rows total from the database starting from the 100th row in the sequence.

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Tuesday, June 6, 2006 11:50 AM by davidacoder
Cool, I was hoping it would work that way :) Great series, by the way!

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Tuesday, June 6, 2006 11:55 AM by davidacoder
@DR: You should also have a look at the video on channel9 on ADO.next and their entity support. I think one of the key points here is that DLINQ is NOT a general O/R mapper, i.e. I don't think the objects you get back from DLINQ will be the business entity objects you might use for complicated business logic. Rather, DLINQ simply exposes the concepts of a relational database as .Net objects. No more, no less. The ADO guys are building another level of abstraction into ado.next that actually exposes business entities. That seems to be a lot more like the traditional O/R mapper products.

I actually don't believe DLINQ is going to change the architecture of LOB apps much, it essentially just changes the syntax for data base access. In a great way, though!

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Tuesday, June 6, 2006 5:06 PM by bahar
I can't figure out how to use DLinq Designer for a web project. It works fine for windows application. you can add DLinqObjects template to your project, but for web applications there is no template!

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Tuesday, June 6, 2006 6:25 PM by ScottGu
Hi Bahar,

For the May CTP drop the DLINQ Designer doesn't support web projects -- that will show up in a future drop.

Until then, you'll want to use the sqlmetal tool I described above.

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Tuesday, June 6, 2006 8:03 PM by gozh2002
With the LINQ as the bridge of object and relational data, is it the strong typed dataset a dying technology?

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Wednesday, June 7, 2006 10:33 AM by Marlon Smith
Will DLINQ support cached resultsets or how could we implement the with DLINQ?

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Wednesday, June 7, 2006 10:50 AM by ScottGu
Hi Marlon,

When you get back a sequence from a DLINQ query, you can cache it if you want (as well as cache the materialized result).

DLINQ also has the ability to "attach" an object for updates later without requiring you to refetch the data.  

This means you could query data from DLINQ, store it in session state, and then 15 minutes later make an update to it and use DLINQ to update the database with it.  This allows pretty powerful multi-page update scenarios.

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Wednesday, June 7, 2006 10:51 AM by ScottGu
Hi Gozh2002,

There is actually a LINQ provider for DataSets -- which means if you are retrieving datasets today you will be able to run LINQ queries against them to retrieve and/or shape data.  So these technologies should be complementary going forward.

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Wednesday, June 7, 2006 10:55 AM by ScottGu
VS,

The big benefit I see for business applications are the following:

1) A much easier and productive way to do data access.  Efficient ADO.NET code for the scenario I performed above would have been 100+ lines of code and not too easy to write.  With LINQ/DLINQ you can express it in 1-2 lines of code total.  This productivity win is huge.

2) The ability to easily shape and transform data from your database/middle-tier.  There are lots of cases where your relational model doesn't map to how you want to express it in business logic -- LINQ/DLINQ make it much easier to-do this (and doesn't require you to transform your business model to accomplish it).

3) Better type-checking and validation of your data and business logic code.  The compiler will fail if you try to access a column that doesn't exist.  With late-bound data technologies (including ADO.NET) you only discover this at runtime.

Note also that DLINQ works fully with SPROCs -- so this doesn't replace them, but also works with them.

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Wednesday, June 7, 2006 4:49 PM by Rishi
The benefits and core concept seem to be great, but am wondering how well will all this scale? For me, it is intrestring to see that you are baking all this in the next ver of .net related technologies - so you must have given a lot of consideration to scalability and applicability? Can you share your thoughts on that? Thanks..

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Wednesday, June 7, 2006 4:57 PM by Marlon Smith
How does this work with disconnected scenerios? I'm writing a smart client app that sends modified clr objects to a service. How does DLINQ keep track of the changes?

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Wednesday, June 7, 2006 11:17 PM by Steve
Excellent.  So I assume ObjectSpaces is definitely dead for VS 2005 and this will come for Orca?

I'm excited about DLinq, but very disapointed in what was considered a 'delay' for VS 2005 is now dropped.

Guess we just hope that DLing doesn't get dropped as well?

FYI - when I installed this CTP, it had problems with (1) I didn't have VB.NET (only C#), the installer has issues and (2) my intellisense didn't work properly until I uninstalled the CTP.

Just FYI for those that might try it.

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Wednesday, June 7, 2006 11:19 PM by Steve
Just to clarify:

"There is actually a LINQ provider for DataSets "

This includes typed datasets correct?

Will I be able to map a LINQ to a ODS control directly with the CRUD support instead of using a tableadapter?

Do you think this will end up replacing the tableadapter?

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Thursday, June 8, 2006 5:54 AM by Anders Matthiessen
Looks great, simple and easy to use.

It looks quite ready, so i wonder if a go-live is in the near future?

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Thursday, June 8, 2006 7:54 AM by Deno2
What are the differences between DLINQ and NHibernate?

Are there advantages to using DLINQ?

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Thursday, June 8, 2006 12:38 PM by ScottGu
Hi Anders,

We will have a go-live license for LINQ/DLINQ before it ships.  The current May CTP doesn't support it officially yet, but it is something we are looking to enable in the future.

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Thursday, June 8, 2006 12:42 PM by ScottGu
Hi Deno2,

LINQ is what really provides the core programming model for the scenario above.  DLINQ is then a specific ORM implementation that you can use with it.

What is nice is that the NHibernate guys will be able to plug-into LINQ as well and provide the nice same programming model too if they choose (as will all other ORM providers).

The choice as to whether to use DLINQ/NHibernate/LLBGen/Paul Wilson's OR Tool/etc then comes down to the specific semantics and mapping models you prefer for your database.  The nice thing is you can get the great productivity and programming model of LINQ like above with all of them.

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Thursday, June 8, 2006 12:51 PM by ScottGu
Hi Rishi,

It will scale and perform very well actually.  Under the covers it uses the same ADO.NET providers that data access uses today -- so things like connection pooling, connection management, and wire-management of SQL queries all use the same optimized code.

DLINQ generates standard SQL queries for the wire, and these queries are result in stateless results (so nothing is being held open on the SQL Server).  As you can see in my scenario above with the data shaping, you have a lot of control over how exactly the SQL looks -- and can automatically trim the columns and data returned.  

Note that in cases where you do want complete control over the SQL used, DLINQ also allows you to explictly define the SQL to execute.  In general you probably won't want/need to-do this that often -- but it is a nice feature to have if you ever need to.

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Thursday, June 8, 2006 12:53 PM by ScottGu
Hi Steve,

Yep -- I believe the LINQ provider for DataSets work with both typed-datasets and untyped-ones.

You can also definitely use the ObjectDataSource control against a class that uses LINQ.  TableAdapters are still obviously going to be supported going forward, although I suspect a lot of people will prefer the LINQ approach.

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Thursday, June 8, 2006 12:58 PM by ScottGu
Hi Marlon,

You might want to ask the disconnected question on the LINQ forums here: http://msdn.microsoft.com/data/ref/linq/

I know that DLINQ allows you to attach disconnected objects and perform updates, but the DLINQ folks on the forums above might have a good sample to point you at.

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Thursday, June 8, 2006 12:59 PM by ScottGu
Hi Marlon,

You might want to ask the disconnected question on the LINQ forums here: http://msdn.microsoft.com/data/ref/linq/

I know that DLINQ allows you to attach disconnected objects and perform updates, but the DLINQ folks on the forums above might have a good sample to point you at.

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Thursday, June 8, 2006 5:22 PM by Steve
Thanks Scott - with Atlas and now LINQ/DLINQ I'm a happy programmer  :)

Speaking of... I'm (1) look forward to the next Atlas CTP - June is here!  :) and (2), as mentioned, I hope the next CTP for Dlinq/Dlinq will have go-live support!

Again, great stuff!  I'm using NHibernate, and I think a graphical tool will be much appreciated in VS 2005

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Thursday, June 8, 2006 5:35 PM by Poppyto
Thanks a lot Scott for precisions about SPROCs & joins (great news for my next developements !).

Maybe a last question :o) : How DLink deals with Left/Right outer joins ?

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Friday, June 9, 2006 11:02 AM by vanram
Scott, great info - thanks. Also really apreciate that you make effort to answer comments/questions and make it a useful/informative discussion - Great...

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Monday, June 12, 2006 12:55 PM by Eric Newton
Looking forward to the next DLinq article...

especially the more complex queries...

for example, I'd like to build a query that can group by ProductCodes on an OrderDate, and sum up how many were ordered on that day, with a sum total.

Let's see that one ;-)

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Wednesday, June 14, 2006 10:57 PM by Al Augustinas
I am interested in creating a provider for Linq. Are spec's or a sample provider available?

An application with which I am involved produces volumes of data at several levels of detail.  Today much of the most detailed data is seldom accessed due to the complexity of working with the data.  In recent months I have identified an improved logical view for the data.  Linq appears as an ideal way to expose the data.

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Thursday, June 22, 2006 1:28 AM by ScottGu
Hi Al,

This site has a bunch of great content on LINQ and DLINQ: http://msdn.microsoft.com/data/ref/linq/

There are also forums available through there, and they should be able to help you create a DLINQ provider.

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Tuesday, July 11, 2006 6:37 AM by kikker46
An error has occurred which the C# compiler is unable to report due to low memory or possible heap corruption. It is recommened that you save all your files, close and restart Visual Studio. That error is keeps popping up with this project :(.

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Monday, July 17, 2006 6:39 PM by ScottGu

Hi jmcfetridge,

It definitely sounds like LINQ and DLINQ would work well for you then. :-)

Thanks,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Friday, July 21, 2006 12:39 PM by jmcfetridge
I guess I was not precise in my questions which are really 3: 1 - if I am starting a project today can DLinq be used as an alternative to Nhibernate and provide the same functionality as I really want to make an either or choice 2. Reporting services does not work real well with an ORM as it prefers working with queries and not objects. Is there an plan to address this. 3. How far off the official release of DLinq

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Saturday, July 22, 2006 12:00 PM by ScottGu

Hi jmcfetridge,

LINQ and DLINQ will ship as part of the "Orcas" release of Visual Studio.  We are starting to ship public tech preview drops of this this summer, and will have betas early next year.  We will also be supporting a go-live license prior to the final release.

If you are starting a new project now it might make sense to wait a little until LINQ/DLINQ is closer to the final release.  But for projects starting in a few more months time, you might be able to use it.

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Saturday, July 22, 2006 12:54 PM by jmcfetridge
Thanks, Scott but hate to a pain as there was a third question " Reporting Services does not work real well with an ORM as it prefers working with queries and not objects. Is there an plan to address this in DLinq"

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Monday, July 24, 2006 1:11 AM by ScottGu

Hi jmcfetridge,

We are building an automated UI scaffolding generator for LINQ (which we call "Blinq") that will help automatically generate CRUD pages for your LINQ model.

I don't know of any direct plans to support Reporting Services against a LINQ model -- although theoretically it is ceretainly possible. My guess is that this will come online at somepoint in the future.

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Tuesday, July 25, 2006 10:16 PM by Chris
Hi, Thanks for the LINQ tutorial. This is definately on my list of things to start learning right after Atlas. Good work! Chris

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Thursday, August 24, 2006 5:37 PM by John McFetridge
I have been writing an article comparing Tableadapters , nHibernate and Linq to SQL(jmcfetridge.blogspot.com) and in it I have standard test that updates a row in a grid through a objectdatasource method that points to a method in my DAL. The Objectdatasource looks like: There is an edit button on the grid row and if the user changes the City to say "Gainesville". The method in the DAL looks like : public void UpdateCustomer(Northwind.Customer cust) { // Save all the changes to database Northwind.Northwind db = new Northwind.Northwind(connStr); //get a new context as this is a new request Northwind.Customer C2 = new Northwind.Customer(); // Tell DLinq to track this object for an update; i.e. not for insertion db.Customers.Attach(cust); db.SubmitChanges(); } then the cust object has the state that we want upon entry as City is now "Gainesville".However the database is not updated. In nHibernate and LLBLgen this is simple as all one needs to do is something like: public void UpdateCustomer(Northwind.Customer cust) { //open a session … _session.Update(cust); } I know that DLinq is not suppossed to be a full blown ORM but this seems pretty fundamental stuff. I tried posting this in Linq forum and no one seems interested.

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Saturday, August 26, 2006 2:52 AM by ScottGu
John, Can you send me an email describing the scenario more? I can then help you to get it working. Thanks, Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Friday, September 15, 2006 10:53 PM by Dave Dolan
I only ask one thing of microsoft on this. PLEASE don't mark all of the functions that do the translation into SQL private or internal! I'd like to be able to override them and generate other querylanguage text from inline queries! (I'm using an associative database that doesn't use SQL at all, but it does use ADO.NET, and I'd like to not be left out in the cold on this one.) Actually, it'd be nice to be able to 'plugin' our own DSL's if we want... any idea if that'll be doable without writing a new compiler? (allowing for a module or extension or add-in or something?)

# re: DLINK support for Win XP home edition

Monday, November 6, 2006 6:51 AM by Sundar
Its a very nice and usefull article. I tried to install the "LINQ May CTP" which i have downloaded from the link metioned in this article in my WinXP Home edition laptop but the installation failed. It asks for Power user account. I hope WinXP Home doesn't have this account. Is there any way i could install this in my XP Home?

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Monday, November 20, 2006 3:49 AM by Tiendq

I've installed LINQ successfully but intellisense is not supported in VS2005.

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Monday, November 20, 2006 10:28 AM by ScottGu

Hi Tiendq,

Intellisense for LINQ isn't supported yet with VS 2005.  We are actively working on intellisense now and expect it to come online next year and start showing up in future Orcas CTPs.

Thanks,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Tuesday, November 21, 2006 12:01 AM by Tiendq

Hi Scott,

Thanks for your reply, BTW, I've read your other article, "Building LINQ class library", so I'd like to ask you more questions:

1. Connection string is hard-coded in DataContext's derived generated class (e.g. Northwind), how can I assign it dynamically? I don't want store same connection string in both website/application's and library's config file.

2. How can I choose to generate XML mapping file instead of using property attribute with DLinq designer?

3. How can I add custom attribute for generated entity classes, e.g. [Serializable]?

Thanks,

Tiendq,

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Tuesday, November 21, 2006 11:22 AM by ScottGu

Hi Tiendq,

1) The DataContext constructor actually has two constructors - one of which takes a connection-string parameter.  Alternatively the default implementation reads the connection-string from the web.config file.

2) I believe the above designer currently uses attribute based mappings.  You can alternatively use the SqlMetal.exe utility that ships with LINQ to use an XML mapping file instead.

3) Probably the best way to add custom attributes to the entity classes would be to add partial class files to the project.  You should then be able to decorate these with any attribute you want.

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Tuesday, November 28, 2006 1:53 AM by Mark

Great blog, can't wait for the next one with better paging implementation.

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Friday, December 1, 2006 8:47 AM by Ted

Hi Scott,

DLinq looks really promising and I'm looking forward to it's final release.  One question though, all of the examples I've ever seen illustrate the use of queries (selects) - can you post some examples of data updates & deletes please?

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Saturday, December 2, 2006 7:00 PM by ScottGu

Hi Ted,

Here is a post I made a few weeks back that includes more details on LINQ.  Some of the samples include insert and update semantics: http://weblogs.asp.net/scottgu/archive/2006/11/04/Tips_2F00_Tricks-and-LINQ-Slides_2B00_Demos-from-my-Talk-in-Dallas.aspx

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Thursday, February 1, 2007 7:00 AM by Muffy J

Hi Scott

How do I write a query like this so

Select * from Customers Where CustomerId Like '%' @CustomerId + '%' using Linq.

Thanks

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Sunday, February 4, 2007 6:22 PM by ScottGu

Hi Muffy,

I believe you can write a query like this:

from p in db.Products

where p.CustomerId.Contains("whatever")

select p;

Hope this helps,

Scott

# re: Using DLINQ with ASP.NET (Part 2 of my LINQ series)

Tuesday, March 13, 2007 11:50 AM by Steve

scott, can you return a dlinq object from a webservice?

Thanks for sharing your feedback! If your feedback doesn't appear right away, please be patient as it may take a few minutes to publish - or longer if the blogger is moderating comments.