Sunday, February 21, 2010

Enhancements in Linq-to-Entities 4

Most of the projects here at Mondo Islamabad are closely coupled with Relational databases. At the back end, these applications must interact with the data represented in a relational form. One-to-one database schemas are not a good choice for large and enterprise applications. The domain/conceptual models of applications mostly differ from the logical models of databases. The Entity Data Model (EDM) is a conceptual data model that can be used to model the data of a particular domain so that applications can interact with data as entities or objects.
Through the EDM, ADO.NET exposes entities as objects in the .NET environment. This makes the object layer an ideal target for Language-Integrated Query (LINQ) support. Therefore, LINQ to ADO.NET includes LINQ to Entities. LINQ to Entities enables developers to write queries against the database from the same language used to build the business logic. The following diagram shows the relationship between LINQ to Entities and the Entity Framework, ADO.NET, and the data source.

EF Enhancements in .NET 4 / Visual Studio 2010 Beta 2

Microsoft will launch Visual Studio 2010 on March 22, 2010. With the release of .NET 4 / Visual Studio 2010 Beta 2 last month, it brought a couple of favorite “data” related features in Entity Framework (EF).
1)    Pluralize Entity: The Entity Framework (EF4) supports the ability to the ability to pluralize entity sets and singularize entities. This means when I refer the table the table will be called “Categories” but the actual class that I work on in my application is a “Category”.
2)    FK Association: The EF4 supports the ability to include “foreign keys” in the data model. In the first version of the Entity Framework if we have a “Category” table that contained a CompanyId which referenced a record in the “Company” table the generated Entity Framework model would convert the CompanyId into a navigation property which would drill directly in the Company table. While this simplified some scenario’s it made web scenarios more difficult.  We felt problems during the translation of LINQ entities to business data transfer objects (DTOs). We should have to write the smart translator in order to perform these conversions.  We have to write the setting logic of entity keys (ugly code) while updating just CompanyId in the Category record. EF4 now includes the ability to have foreign key columns added directly to the entity. It became flexible by allowing both the options to either set the foreign key (CompanyId) or the navigation property (Category.Company) making disconnected entities scenarios easier to program in N-tier and enterprise applications.
3)     EnableDynamicData Extension Method:  With VS2010 Beta 2 they have added a new feature of Dynamic Data which can be enabled on our data controls like this:


This one line of code will automatically bring many of the features that Dynamic Data provides:
  1. Automatic validation
  2. Support for Data Annotations on objects to control validation and display properties
  3. Support for field templates for customizing UI behavior based on data type
This should allow any developer to utilize the power of Dynamic Data without radically changing their application or requiring Linq to SQL or Entity Framework.

LINQ to Entity 4.0, WCF and Disconnected Entities

We have used entity frameworks in the centre’s projects more than one a year.  It best fits for N-tier applications to be used along with WCF. The architecture is almost the same as it should be for an N-tier application. Client side is Win Form, WebPages or Silverlight. After client layer, we have WCF service proxy which decouples the client from WCF Service logic. Off course I have used WCF to make it multitier/enterprise application, remotely accessible to provide services to the client tier. Service layer uses Business Layer to handle the Data Access from database.  Data Access Layer is EF4 and these entities are translated into serializable Business Objects and are passed over all the layers till client as DTO.

I wonder sometimes that there is a no need to provide an abstraction to EF4 as it is already on such a high level. You can see the code I have written in EF4 using VS2010. At the business layer, how simple it is to write the pseudo code.

a)      Create a new Category and FK Association to an existing Company by setting the FK Property directly:
using (var context = new EzDoxEntities())
    //Create a category and a relationship to a known company by Id
    Category c = new Category
        Id = 1,
        Name = "Beverges",
        CompanyId = 7
    //Add the product (and create the relationship by FK value)

Create a new Category and a new FK Association to an existing Company by setting the reference instead:

public void CreateCategory () 

    using (var context = new EzDoxEntities ()) 
        //Create a new product and relate to an existing category
        Product p = new Product         { 
            ID = 1, 
            Name = "Bovril", 
            Company = context.Comapnies.Single(c => c.Id == 7) 
        // Note: no need to add the category into context, because relating   
        // to an existing company does that automatically.        
       // Also notice the use of the Single() query operator       
       // this is new to EF4 too.        
c)      Update an existing Category without informing the Entity Framework about the original value of the CompanyId  through entity key: (it is a concept of stub entity)

public void UpdateCategory(Category editedCategory) 

    using (var context = new EzDoxEntities()) 
       // Create a stand-in for the original entity
       // by just using the Id. Of the editedCategory
       // -or- Create a stub entity and attach it.
            new Category { Id = editedCategory.Id }); 

        // Now update with new values including CategoryID                       

In this example "editedCategory" is a category which has been edited somewhere, this is exactly the sort of the code you might write in the Update method of an ASP.NET MVC controller, and is a great improvement over the code you have to write using Independent Associations in EF1.

The above code snippet Attach statement [var category = new Category { ID = editedCategory.ID }] creates a “Stub Entity”. A stub entity is a partially populated entity that stands in for the real thing.

d)      Deletion is so simple by using stub entity as

public void DeleteCategory(long categoryId)
        using (var context = new EzDoxEntities())
            // Stub entity 
            var category = new DAL.Category { Id = categoryId };
            //Attach the new entity
            //Delete the entity now
We remember the days when we used to implement the database layer using ADO.NET and then build the objects using the data or use the typed dataset instead. We were comfortable at that time to work with these approaches. But now ease of the use and maturity of EF4 has changed the perception altogether.  There is a very minimum use of stored procedures in data model and major logic is based on the integrated quires which are so simple and fast for maintenance. There is a slight performance degradation using EF4 as compared to regular ADO.NET data readers.  But again it depends on the situation and I can see a lot of abstraction and simplicity in the code and maintenance when using EF4.


Monday, February 1, 2010

Using Metadata to query Tables and Columns views in SQL Server

The Information Schema views are part of the SQL-92 standard.  There are viewes like TABLES, COLUMNS that provides information about the tables and columns in a database.

You can write a query these viewes to get metadata of "all tables and columns in the database".
 Just open database SQL window and execute following query:

Select  TABLE_CATALOG as [DB],
        TABLE_NAME as [Table],
        COLUMN_NAME as [Column],
        DATA_TYPE as [Type],
from information_schema.columns
    select  TABLE_NAME

    from information_schema.tables
    where table_type = 'base table'
order by TABLE_NAME

You will see following rows in result set: