According to Wikipedia, Object-relational mapping is:

a programming technique for converting data between incompatible type systems in relational databases and object-oriented programming languages.

This is the LINQ to SQL sample code at the beginning of this series:

using (NorthwindDataContext database = new NorthwindDataContext())
{
    var results = from product in database.Products
                  where product.Category.CategoryName == "Beverages"
                  select new
                  {
                      product.ProductName,
                      product.UnitPrice
                  };
    foreach (var item in results)
    {
        Console.WriteLine(
            "{0}: {1}", 
            item.ProductName, 
            item.UnitPrice.ToString(CultureInfo.InvariantCulture));
    }
}

According to this post, the above query expression will be compiled to query methods:

var results = database.Products.Where(product => product.Category.CategoryName == "Beverages")
                               .Select(product => new
                                                      {
                                                          product.ProductName,
                                                          product.UnitPrice
                                                      });

It is querying the ProductName and UnitPrice fields of the Products table in the Northwind database, which belong to the specified CategoryName. To work with SQL Server representations (fields, tables, databases) in C# representations (object models), the mappings between SQL representations and C# representations need to be created. LINQ to SQL provides an Object-relational mapping designer tool to create those objects models automatically.

Create C# models from SQL schema

The easiest way of modeling is to use Visual Studio IDE. This way works with:

• SQL Server 2000
• SQL Server 2005
• SQL Server 2008
• SQL Server 2008 R2

Take the Northwind database as an example. First, setup a data connection to the Northwind database:

Then, create a “LINQ to SQL Classes” item to the project:

By creating a Northwind.dbml file, the O/R designer is opened:

Since the above query works with the Products table and the Categories table, just drag the 2 tables and drop to the O/R designer:

In the designer, the modeling is done. Please notice that the foreign key between Categories table and Products table is recognized, and the corresponding association is created in the designer.

Now the object models are ready to rock. Actually the designer has automatically created the following C# code:

• Category class: represents each record in Categories table;
  • CategoryID property (an int): represents the CategoryID field; So are the other properties shown above;
  • Products propery (a collection of Product object): represents the associated many records in Products table
• Product class: represents each record in Products table;
  • ProductID property (an int): represents the ProductID field; So are the other properties shown above;
  • Category propery (a Category object): represents the associated one records in Products table;
• NorthwindDataContext class: represents the Northwind database;
  • Categories property (a collection of the Category objects): represents the Categories table;
  • Products property (a collection of the Product objects): represents the Products table;

Besides, database, tables, fields, other SQL stuff can also be modeled by this O/R designer:

Another way to generate the models is to use the command line tool SqlMetal.exe. Please check MSDN for details of code generation.

And, please notice that, the Category entity class is generated from the Categories table. Here plural name is renamed to singular name, because a Category object is the mapping of one record of Categories table. This can be configured in Visual Studio:

Implement the mapping

Now take a look at how the SQL representations are mapped to C# representations.

The Northwind.dbml is nothing but an XML file:

<?xml version="1.0" encoding="utf-8"?>
<!-- [Northwind] database is mapped to NorthwindDataContext class. -->
<Database Name="Northwind" Class="NorthwindDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
    <!-- Connection string -->
    <Connection Mode="WebSettings" ConnectionString="Data Source=qablog.qaitdevlabs.com;Initial Catalog=Northwind;Integrated Security=True" SettingsObjectName="System.Configuration.ConfigurationManager.ConnectionStrings" SettingsPropertyName="NorthwindConnectionString" Provider="System.Data.SqlClient" />

    <!-- Categories property is a member of NorthwindDataContext class. -->
    <Table Name="dbo.Categories" Member="Categories">
        <!-- [Categories] table is mapped to Category class. -->
        <Type Name="Category">
            <!-- [CategoryID] (SQL Int) field is mapped to CategoryID property (C# int). -->
            <Column Name="CategoryID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
            <!-- [CategoryName] (SQL NVarChar(15)) field is mapped to CategoryName property (C# string). -->
            <Column Name="CategoryName" Type="System.String" DbType="NVarChar(15) NOT NULL" CanBeNull="false" />
            <!-- Other fields. -->
            <Column Name="Description" Type="System.String" DbType="NText" CanBeNull="true" UpdateCheck="Never" />
            <Column Name="Picture" Type="System.Data.Linq.Binary" DbType="Image" CanBeNull="true" UpdateCheck="Never" />
            <!-- [Categories] is associated with [Products] table via a foreign key.
            So Category class has a Products peoperty to represent the associated many Product objects. -->
            <Association Name="Category_Product" Member="Products" ThisKey="CategoryID" OtherKey="CategoryID" Type="Product" />
        </Type>
    </Table>

    <!-- Products property is a member of NorthwindDataContext class. -->
    <Table Name="dbo.Products" Member="Products">
        <!-- [Products] table is mapped to Product class. -->
        <Type Name="Product">
            <!-- Fields. -->
            <Column Name="ProductID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
            <Column Name="ProductName" Type="System.String" DbType="NVarChar(40) NOT NULL" CanBeNull="false" />
            <Column Name="SupplierID" Type="System.Int32" DbType="Int" CanBeNull="true" />
            <Column Name="CategoryID" Type="System.Int32" DbType="Int" CanBeNull="true" />
            <Column Name="QuantityPerUnit" Type="System.String" DbType="NVarChar(20)" CanBeNull="true" />
            <Column Name="UnitPrice" Type="System.Decimal" DbType="Money" CanBeNull="true" />
            <Column Name="UnitsInStock" Type="System.Int16" DbType="SmallInt" CanBeNull="true" />
            <Column Name="UnitsOnOrder" Type="System.Int16" DbType="SmallInt" CanBeNull="true" />
            <Column Name="ReorderLevel" Type="System.Int16" DbType="SmallInt" CanBeNull="true" />
            <Column Name="Discontinued" Type="System.Boolean" DbType="Bit NOT NULL" CanBeNull="false" />
            <!-- [Products] is associated with [Products] table via a foreign key.
            So Product class has a Category peoperty to represent the associated one Category object. -->
            <Association Name="Category_Product" Member="Category" ThisKey="CategoryID" OtherKey="CategoryID" Type="Category" IsForeignKey="true" />
        </Type>
    </Table>
</Database>

It describes how the SQL stuff are mapped to C# stuff.

A Northwind.dbml.layout file is created along with the dbml. It is also an XML, describing how the O/R designer should visualize the objects models:

<?xml version="1.0" encoding="utf-8"?>
<ordesignerObjectsDiagram dslVersion="1.0.0.0" absoluteBounds="0, 0, 11, 8.5" name="Northwind">
    <DataContextMoniker Name="/NorthwindDataContext" />
    <nestedChildShapes>
        <!-- Category class -->
        <classShape Id="81d67a31-cd80-4a91-84fa-5d4dfa2e8694" absoluteBounds="0.75, 1.5, 2, 1.5785953776041666">
            <DataClassMoniker Name="/NorthwindDataContext/Category" />
            <nestedChildShapes>
                <!-- Properties -->
                <elementListCompartment Id="a261c751-8ff7-471e-9545-cb385708d390" absoluteBounds="0.765, 1.96, 1.9700000000000002, 1.0185953776041665" name="DataPropertiesCompartment" titleTextColor="Black" itemTextColor="Black" />
            </nestedChildShapes>
        </classShape>

        <!-- Product class -->
        <classShape Id="59f11c67-f9d4-4da9-ad0d-2288402ec016" absoluteBounds="3.5, 1, 2, 2.7324039713541666">
            <DataClassMoniker Name="/NorthwindDataContext/Product" />
            <nestedChildShapes>
                <!-- Properties -->
                <elementListCompartment Id="6c1141a2-f9a9-4660-8730-bed7fa15bc27" absoluteBounds="3.515, 1.46, 1.9700000000000002, 2.1724039713541665" name="DataPropertiesCompartment" titleTextColor="Black" itemTextColor="Black" />
            </nestedChildShapes>
        </classShape>

        <!-- Association arrow -->
        <associationConnector edgePoints="[(2.75 : 2.28929768880208); (3.5 : 2.28929768880208)]" fixedFrom="Algorithm" fixedTo="Algorithm">
            <AssociationMoniker Name="/NorthwindDataContext/Category/Category_Product" />
            <nodes>
                <!-- From Category class -->
                <classShapeMoniker Id="81d67a31-cd80-4a91-84fa-5d4dfa2e8694" />
                <!-- To Product class -->
                <classShapeMoniker Id="59f11c67-f9d4-4da9-ad0d-2288402ec016" />
            </nodes>
        </associationConnector>
    </nestedChildShapes>
</ordesignerObjectsDiagram>

A Northwind.designer.cs is also created, containing the auto generated C# code.

This is how the NorthwindDataContext looks like:

[Database(Name = "Northwind")]
public partial class NorthwindDataContext : DataContext
{
    public Table<Category> Categories
    {
        get
        {
            return this.GetTable<Category>();
        }
    }

    public Table<Product> Products
    {
        get
        {
            return this.GetTable<Product>();
        }
    }
}

And this is the Category class:

[Table(Name = "dbo.Categories")]
public partial class Category : INotifyPropertyChanging, INotifyPropertyChanged
{
    private int _CategoryID;

    private EntitySet<Product> _Products;

    [Column(Storage = "_CategoryID", AutoSync = AutoSync.OnInsert, 
        DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)]
    public int CategoryID
    {
        get
        {
            return this._CategoryID;
        }
        set
        {
            if ((this._CategoryID != value))
            {
                this.OnCategoryIDChanging(value);
                this.SendPropertyChanging();
                this._CategoryID = value;
                this.SendPropertyChanged("CategoryID");
                this.OnCategoryIDChanged();
            }
        }
    }

    // Other properties.

    [Association(Name = "Category_Product", Storage = "_Products", 
        ThisKey = "CategoryID", OtherKey = "CategoryID")]
    public EntitySet<Product> Products
    {
        get
        {
            return this._Products;
        }
        set
        {
            this._Products.Assign(value);
        }
    }
}

The Products looks similar.

Customize the mapping

Since the mapping info are simply stored in the XML file and C# code, they can be customized in the O/R designer easily:

After renaming Category class to CategoryEntity, the XML and C# is refined automatically:

<?xml version="1.0" encoding="utf-8"?>
<Database Name="Northwind" Class="NorthwindDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
    <Table Name="dbo.Categories" Member="CategoryEntities">
        <Type Name="CategoryEntity">
            <!-- Fields -->
        </Type>
    </Table>
    <Table Name="dbo.Products" Member="Products">
        <Type Name="Product">
            <!-- Fields -->
            <Association Name="Category_Product" Member="CategoryEntity" Storage="_Category" ThisKey="CategoryID" OtherKey="CategoryID" Type="CategoryEntity" IsForeignKey="true" />
        </Type>
    </Table>
</Database>

and

[Database(Name = "Northwind")]
public partial class NorthwindDataContext : DataContext
{
    public Table<CategoryEntity> CategoryEntities { get; }
}

[Table(Name = "dbo.Categories")]
public partial class CategoryEntity : INotifyPropertyChanging, INotifyPropertyChanged
{
}

[Table(Name = "dbo.Products")]
public partial class Product : INotifyPropertyChanging, INotifyPropertyChanged
{
    [Association(Name = "Category_Product", Storage = "_Category",
        ThisKey = "CategoryID", OtherKey = "CategoryID", IsForeignKey = true)]
    public CategoryEntity CategoryEntity { get; set; }
}

Properties, associations, and inheritances and also be customized:

For example, The ProductID property can be renamed to ProductId to be compliant to .NET Framework Design Guidelines.

More options are available to customize the data context, entities, and properties:

Please notice this mapping is one way mapping, from SQL Server to C#. When the mapping information is changed in O/R designer, SQL Server is not affected at all.

And, LINQ to SQL is designed to provide a simple O/R mapping, not supporting advenced functionalities, like multi-table inheritance, etc. According to MSDN:

The single-table mapping strategy is the simplest representation of inheritance and provides good performance characteristics for many different categories of queries.

Please check this link for more details.

Work with the models

The auto generated models are very easy and extensible.

Partial class

All the generated C# classes are partial classes. For example, it is very easy to add a NorthwindDataContext,cs file and a Category.cs file to the project, and write the extension code.

Partial method

There are also a lot of partial method in the generated code:

[Database(Name = "Northwind")]
public partial class NorthwindDataContext : DataContext
{
    #region Extensibility Method Definitions

    partial void OnCreated();
    partial void InsertCategory(Category instance);
    partial void UpdateCategory(Category instance);
    partial void DeleteCategory(Category instance);
    partial void InsertProduct(Product instance);
    partial void UpdateProduct(Product instance);
    partial void DeleteProduct(Product instance);

    #endregion
}

For example, the OnCreated() can be implemented in the NorthwindDataContext,cs:

public partial class NorthwindDataContext
{
    // OnCreated will be invoked by constructors.
    partial void OnCreated()
    {
        // The default value is 30 seconds.
        this.CommandTimeout = 40;
    }
}

When the Northwind is constructed, the OnCreated() is invoked, and the custom code is executed.

So are the entities:

[Table(Name = "dbo.Categories")]
public partial class Category : INotifyPropertyChanging, INotifyPropertyChanged
{
    #region Extensibility Method Definitions

    partial void OnLoaded();
    partial void OnValidate(ChangeAction action);
    partial void OnCreated();
    partial void OnCategoryIDChanging(int value);
    partial void OnCategoryIDChanged();
    partial void OnCategoryNameChanging(string value);
    partial void OnCategoryNameChanged();
    partial void OnDescriptionChanging(string value);
    partial void OnDescriptionChanged();
    partial void OnPictureChanging(Binary value);
    partial void OnPictureChanged();

    #endregion
}

For example, the OnValidated() is very useful for the data correction:

[Table(Name = "dbo.Categories")]
public partial class Category
{
    partial void OnValidate(ChangeAction action)
    {
        switch (action)
        {
            case ChangeAction.Delete:
                // Validates the object when deleted.
                break;
            case ChangeAction.Insert:
                // Validates the object when inserted.
                break;
            case ChangeAction.None:
                // Validates the object when not submitted.
                break;
            case ChangeAction.Update:
                // Validates the object when updated.
                if (string.IsNullOrWhiteSpace(this._CategoryName))
                {
                    throw new ValidationException("CategoryName is invalid.");
                }
                break;
            default:
                break;
        }
    }
}

When the category object (representing a record in Categories table) is updated, the custom code checking the CategoryName will be executed.

And, because each entity class’s Xxx property’s setter involves OnXxxChanging() partial method:

[Table(Name = "dbo.Categories")]
public partial class CategoryEntity : INotifyPropertyChanging, INotifyPropertyChanged
{
    [Column(Storage = "_CategoryName", DbType = "NVarChar(15) NOT NULL", CanBeNull = false)]
    public string CategoryName
    {
        get
        {
            return this._CategoryName;
        }
        set
        {
            if ((this._CategoryName != value))
            {
                this.OnCategoryNameChanging(value);
                this.SendPropertyChanging();
                this._CategoryName = value;
                this.SendPropertyChanged("CategoryName");
                this.OnCategoryNameChanged();
            }
        }
    }
}

Validation can be also done in this way:

public partial class CategoryEntity
{
    partial void OnCategoryNameChanging(string value)
    {
        if (string.IsNullOrWhiteSpace(value))
        {
            throw new ArgumentOutOfRangeException("value");
        }
    }
}

INotifyPropertyChanging and INotifyPropertyChanged interfaces

Each auto generated entity class implements INotifyPropertyChanging and INotifyPropertyChanged interfaces:

namespace System.ComponentModel
{
    public interface INotifyPropertyChanging
    {
        event PropertyChangingEventHandler PropertyChanging;
    }

    public interface INotifyPropertyChanged
    {
        event PropertyChangedEventHandler PropertyChanged;
    }
}

For example, in the above auto generated CategoryName code, after setting the CategoryName, SendPropertyChanged() is invoked, passing the propery name “CategoryName” as argument:

[Table(Name = "dbo.Categories")]
public partial class CategoryEntity : INotifyPropertyChanging, INotifyPropertyChanged
{
    public event PropertyChangedEventHandler PropertyChanged;

    protected virtual void SendPropertyChanged(String propertyName)
    {
        if (this.PropertyChanged != null)
        {
            this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }
    }
}

This is very useful to track changes of the entity object:

using (NorthwindDataContext database = new NorthwindDataContext())
{
    Category category = database.Categories.Single(item => item.CategoryName = "Beverages");
    category.PropertyChanged += (_, e) =>
        {
            Console.Write("Propery {0} is changed", e.PropertyName);
        };

    // Work with the category object.
    category.CategoryID = 100;
    // ...
}

And this is used for change tracking by DataContext, which will be explained later.

Programmatically access the mapping information

The mapping information is stored in DataContext.Mapping as a MetaModel object. Here is an example:

public static class DataContextExtensions
{
    public static Type GetEntityType(this DataContext database, string tableName)
    {
        return database.Mapping.GetTables()
                               .Single(table => table.TableName.Equals(
                                   tableName, StringComparison.Ordinal))
                               .RowType
                               .Type;
    }
}

The method queries the mapping information with the table name, and returns the entity type:

using (NorthwindDataContext database = new NorthwindDataContext())
{
    Type categoryType = database.GetEntityType("dbo.Categories");
}

Create SQL schema from C# models

Usually, many people design the SQL database first, then model it with the O/R designer, and write code to work with the C# object models. But this is not required. It is totally Ok to create POCO models first without considering the SQL stuff:

public partial class Category
{
    public int CategoryID { get; set; }

    public string CategoryName { get; set; }

    public EntitySet<Product> Products { get; set; }
}

Now it is already able to start coding with this kind of models.

Later, there are 2 ways to integrate the C# program with SQL Server database:

• Generate object models from designed SQL Server database;
• Decorate POCO models with mapping attributes, Invoke CreateDatabase() method of DataContext to create the expected database schema in SQL Server.

For example, the C# models can be polluted with O/R mapping knowledge like this:

[Table(Name = "Categories")]
public class Category
{
    [Column(DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true)]
    public int CategoryId { get; set; }

    [Column(DbType = "NVarChar(15) NOT NULL")]
    public string CategoryName { get; set; }

    [Association(Name = "Category_Products",
        ThisKey = "CategoryId", OtherKey = "CategoryId")]
    public EntitySet<Product> Products { get; set; }
}

[Table(Name = "Products")]
public class Product
{
    [Column(DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true)]
    public int ProductId { get; set; }

    [Column(DbType = "NVarChar(40) NOT NULL")]
    public string ProductName { get; set; }

    [Column(DbType = "Int")]
    public int CategoryId { get; set; }

    [Association(Name = "Category_Products", IsForeignKey = true,
        ThisKey = "CategoryId", OtherKey = "CategoryId")]
    public Category Category { get; set; }
}

[Database(Name = "SimpleNorthwind")]
public class SimpleNorthwindDataContext : DataContext
{
    public SimpleNorthwindDataContext(IDbConnection connection)
        : base(connection)
    {
    }

    public Table<Category> Categories { get; set; }

    public Table<Product> Products { get; set; }
}

Now it is ready to create database schema in SQL server:

using (SimpleNorthwindDataContext database = new SimpleNorthwindDataContext(new SqlConnection(
    @"Data Source=qablog.qaitdevlabs.com;Initial Catalog=SimpleNorthwind;Integrated Security=True")))
{
    if (database.DatabaseExists())
    {
        database.DeleteDatabase();
    }

    database.CreateDatabase();
}

Isn’t this easy? This is the generated SimpleNorthwind database in SQL Server:

Lazy loading is a nice and very important concept in the programming world. Sometimes it helps to improve performance and adapt best practices in application design. Let’s discuss why lazy loading is useful and how it helps to develop a high performance application.

Lazy loading is essential when the cost of object creation is very high and the use of the object is vey rare. So, this is the scenario where it’s worth implementing lazy loading.
The fundamental idea of lazy loading is to load object/data when needed.

At first we will implement a traditional concept of loading (it’s not lazy loading) and then we will try to understand the problem in this. Then we will implement lazy loading to solve the problem.

Have a look at the following code.

Implement lazy loading using Lazy<T> class

As we know, lazy loading is a nice feature of applications, not only to improve the performance of the application but also it helps to manage memory and other resource efficiently. Basically we can use lazy initialization when a large object is created or the execution of a resource- intensive task in particular when such creation or execution might not occur during the lifetime of the program.

And there are many choices to implement lazy initialization. We can use our own implementation to delay object population or we can use the Lazy<T> class of the .NET library to do it.

To prepare for lazy initialization, you create an instance of Lazy<T>. The type argument of the Lazy<T> object that you create specifies the type of the object that you want to initialize lazily. The constructor that you use to create the Lazy<T> object determines the characteristics of the initialization. Lazy initialization occurs the first time the Lazy<T>.Value property is accessed.

The Lazy<T> class contains two properties by which we can detect the status of the lazy class.

IsValueCreated

This property will tell us whether or not the value is initializing in a lazy class.

Value

It gets the lazy initialized value of the current Lazy<T> instance.

Fine. We will now implement one simple class and we will see how lazy<T> works with it. Have a look at the following example.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace ConsoleAPP

{

public class Test

{

private List<string> list = null;

public Test()

{

Console.WriteLine(“List Generated:”);

list = new List<string>() {

“Sourav”,”Ram”

};

}

public List<string> Names

{

get

{

return list;

}

}

}

class Program

{

static void Main(string[] args)

{

Lazy<Test> lazy = new Lazy<Test>();

Console.WriteLine(“Data Loaded : ” + lazy.IsValueCreated);

Test t = lazy.Value;

foreach (string tmp in t.Names)

{

Console.WriteLine(tmp);

}

Console.ReadLine();

}

}

}
The Test class has been declared and the instance (lazy) has created a Lazy<T> class. We are then checking whether the value is populated or not in the Test class. In the output we are seeing the value is “False” so, the value is still not populated.

Whenever the line “Test t = lazy.Value” executes, the value will be populated in the Test class and this is how the Test class will initialize lazily.

In the next line we are accessing the property of the test class that will return a string array and we are printing it. Here is sample output in the following.

The question may occur to you, is lazy<T> thread safe ?

By default, all public and protected members of the Lazy<T> class are thread safe and may be used concurrently from multiple threads. These thread-safety guarantees may be removed optionally and per instance, using parameters to the type’s constructors.

Thanks for reading, Happy learning. In the next article we will see how to implement lazy loading in Entity Framework.

Microsoft announced the new Azure Data Lake services for analytics in the cloud that includes a hyper-scale repository, a new analytics service built on YARN that allows data developers and data scientists to analyze all data, and HDInsight, a fully managed Hadoop, Spark, Storm and HBase service. Azure Data Lake Analytics includes U-SQL, a language that unifies the benefits of SQL with the expressive power of your own code. U-SQL’s scalable distributed query capability enables you to efficiently analyze data in the store and across relational stores such as Azure SQL Database. In this blog post I will outline the motivation for U-SQL, some of our inspiration, and design philosophy behind the language, and show you a few examples of the major aspects of the language.

Why U-SQL?

If you analyze the characteristics of Big Data analytics, several requirements arise naturally for an easy to use, yet powerful language:

• Process any type of data. From analyzing BotNet attack patterns from security logs to extracting features from images and videos for machine learning, the language needs to enable you to work on any data.
• Use custom code easily to express your complex, often proprietary business algorithms. The example scenarios above may all require custom processing that is often not easily expressed in standard query languages, ranging from user defined functions, to custom input and output formats.
• Scale efficiently to any size of data without you focusing on scale-out topologies, plumbing code, or limitations of a specific distributed infrastructure.

How do existing Big Data languages stack up to these requirements?

SQL-based languages (such as Hive and others) provide you with a declarative approach that natively does the scaling, parallel execution, and optimizations for you. This makes them easy to use, familiar to a wide range of developers, and powerful for many standard types of analytics and warehousing. However, their extensibility model and support for non-structured data and files are often bolted on and harder to use. For example, even if you just want to quickly explore your data in a file or remote data source, you need to create catalog objects to schematize file data or remote sources before you can query them, which reduces your agility. And although SQL-based languages often have several extensibility points for custom formatters, user-defined functions, and aggregators, they are rather complex to build, integrate, and maintain, with varying degrees of consistency in the programming models.

Programming language-based approaches to process Big Data, for their part, provide an easy way to add your custom code. However, a programmer often has to explicitly code for scale and performance, often down to managing the execution topology and workflow such as the synchronization between the different execution stages or the scale-out architecture. This code can be difficult to write correctly, and optimized for performance. Some frameworks support declarative components such as language integrated queries or embedded SQL support. However, SQL may be integrated as strings and thus lacking tool support, the extensibility integration may be limited or – due to the procedural code that does not guard against side-effects – hard to optimize, and does not provide for reuse.

Taking the issues of both SQL-based and procedural languages into account, we designed U-SQL from the ground-up as an evolution of the declarative SQL language with native extensibility through user code written in C#. This unifies both paradigms, unifies structured, unstructured, and remote data processing, unifies the declarative and custom imperative coding experience, and unifies the experience around extending your language capabilities.

U-SQL is built on the learnings from Microsoft’s internal experience with SCOPE and existing languages such as T-SQL, ANSI SQL, and Hive. For example, we base our SQL and programming language integration and the execution and optimization framework for U-SQL on SCOPE, which currently runs hundred thousands of jobs each day internally. We also align the metadata system (databases, tables, etc.), the SQL syntax, and language semantics with T-SQL and ANSI SQL, the query languages most of our SQL Server customers are familiar with. And we use C# data types and the C# expression language so you can seamlessly write C# predicates and expressions inside SELECT statements and use C# to add your custom logic. Finally, we looked to Hive and other Big Data languages to identify patterns and data processing requirements and integrate them into our framework.

In short, basing U-SQL language on these existing languages and experiences should make it easy for you to get started and powerful enough for the hardest problems.

Show me U-SQL!

Let’s assume that I have downloaded my Twitter history of all my tweets, retweets, and mentions as a CSV file and placed it into my Azure Data Lake Store.

In this case I know the schema of the data I want to process, and for starters I want to just count the number of tweets for each of the authors in the tweet “network”:

@t = EXTRACT date string

, time string

, author string

, tweet string

FROM “/input/MyTwitterHistory.csv”

USING Extractors.Csv();

 

@res = SELECT author

, COUNT(*) AS tweetcount

FROM @t

GROUP BY author;

 

OUTPUT @res TO “/output/MyTwitterAnalysis.csv”

ORDER BY tweetcount DESC

USING Outputters.Csv();

The above U-SQL script shows the three major steps of processing data with U-SQL:

1. Extract data from your source. Note that you just schematize it in your query with the EXTRACT statement. The datatypes are based on C# datatypes and I use the built-in Extractors library to read and schematize the CSV file.
2. Transform using SQL and/or custom user defined operators (which we will cover another time). In the example above, it is a familiar SQL expression that does a GROUP BY aggregation.
3. Output the result either into a file or into a U-SQL table to store it for further processing.

Note that U-SQL’s SQL keywords have to be upper-case to provide syntactic differentiation from syntactic C# expressions with the same keywords but different meaning.

Also notice that each of the expressions are assigned to a variable (@t and @res). This allows U-SQL to incrementally transform and combine data step by step expressed as an incremental expression flow using functional lambda composition (similar to what you find in the Pig language). The execution framework, then, composes the expressions together into a single expression. That single expression can then be globally optimized and scaled out in a way that isn’t possible if expressions are being executed line by line. The following picture shows you the graph generated for the next query in this blog post:

Going back to our example, I now want to add additional information about the people mentioned in the tweets and extend my aggregation to return how often people in my tweet network are authoring tweets and how often they are being mentioned. Because I can use C# to operate on the data, I can use an inline C# LINQ expression to extract the mentions into an ARRAY. Then I turn the array into a rowset with EXPLODE and apply the EXPLODE to each row’s array with a CROSS APPLY. I union the authors with the mentions, but need to drop the leading @-sign to align it with the author values. This is done with another C# expression where I take the Substring starting at position 1.

@t = EXTRACT date string

, time string

, author string

, tweet string

FROM “/input/MyTwitterHistory.csv”

USING Extractors.Csv();

@m = SELECT new SQL.ARRAY<string>(

tweet.Split(‘ ‘).Where(x => x.StartsWith(“@”))) AS refs

FROM @t;

 

@t = SELECT author, “authored” AS category

FROM @t

UNION ALL

SELECT r.Substring(1) AS r, “mentioned” AS category

FROM @m CROSS APPLY EXPLODE(refs) AS Refs(r);

 

@res = SELECT author

, category

, COUNT(*) AS tweetcount

FROM @t

GROUP BY author, category;

 

OUTPUT @res TO “/output/MyTwitterAnalysis.csv”

ORDER BY tweetcount DESC

USING Outputters.Csv();

As a next step I can use the Azure Data Lake Tools for Visual Studio to refactor the C# code into C# functions using the tool’s code-behind functionality. When I then submit the script, it automatically deploys the code to the service.

I can also deploy and register the code as an assembly in my U-SQL metadata catalog. This allows me and other people to use the code in future scripts. The following script shows how to refer to the functions, assuming the assembly was registered as TweetAnalysis:

REFERENCE ASSEMBLY TweetAnalysis;

 

@t = EXTRACT date string

, time string

, author string

, tweet string

FROM “/input/MyTwitterHistory.csv”

USING Extractors.Csv();

 

@m = SELECT Tweets.Udfs.get_mentions(tweet) AS refs

FROM @t;

 

@t = SELECT author, “authored” AS category

FROM @t

UNION ALL

SELECT Tweets.Udfs.cleanup_mentions(r) AS r, “mentioned” AS category

FROM @m CROSS APPLY EXPLODE(refs) AS Refs(r);

 

@res = SELECT author

, category

, COUNT(*) AS tweetcount

FROM @t

GROUP BY author, category;

 

OUTPUT @res

TO “/output/MyTwitterAnalysis.csv”

ORDER BY tweetcount DESC

USING Outputters.Csv();

Because I noticed that I need to do a bit more cleanup around the mentions besides just dropping the @ sign, the assembly also contains a cleanup_mentions functions that does additional processing beyond dropping the @.

This is why U-SQL!

I hope you got a glimpse at why we think U-SQL makes it easy to query and process Big Data and that you understand our thinking behind the language. Over the next couple of weeks we will be expanding more on the language design philosophy and provide more sample code and scenarios over at our Big Data topic in the Azure blog. We’ll also dive in deeper into many of the additional capabilities such as:

• Operating over set of files with patterns
• Using (Partitioned) Tables
• Federated Queries against Azure SQL DB
• Encapsulating your U-SQL code with Views, Table-Valued Functions, and Procedures
• SQL Windowing Functions
• Programming with C# User-defined Operators (custom extractors, processors)
• Complex Types (MAP, ARRAY)
• Using U-SQL in data processing pipelines
• U-SQL in a lambda architecture for IOT analytics

U-SQL makes Big Data processing easy because it:

• Unifies declarative queries with the expressiveness of your user code
• Unifies querying structured and unstructured data
• Unifies local and remote queries
• Increases productivity and agility from Day 1 for YOU!

Not Just U-SQL – Azure Data Lake provides Productivity on All Your Data

U-SQL is just one of the ways that we are working to make Azure Data Lake the most productive environment for authoring, debugging and optimizing analytics at any scale. With rich support for authoring and monitoring Hive jobs, a C# based authoring model for building Storm jobs for real time streaming, and supporting every stage of the job lifecycle from development to operationalization, the Azure Data Lake services let you focus more on the questions you want to answer than spending time debugging distributed infrastructure. Our goal is to make big data technology simpler and more accessible to the greatest number of people possible: big data professionals, engineers, data scientists, analysts and application developers.

Lambda. In lambda calculus, a function becomes a variable. Behavior is now just another unit of data. In programs we apply the => operator to indicate a lambda.

Syntax. To the left, we have arguments. The result is on the right. Often we pass lambda expressions as arguments, for sorting or for searching. We use them in queries.

An example. Perhaps the most common place to use lambdas is with List. Here we use FindIndex, which receives a Predicate method. We specify this as a lambda expression.

Based on: .NET 4.5

C# program that uses lambda, List

using System;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
	List<int> elements = new List<int>() { 10, 20, 31, 40 };
	// ... Find index of first odd element.
	int oddIndex = elements.FindIndex(x => x % 2 != 0);
	Console.WriteLine(oddIndex);
    }
}

Output

2

Lambda details

x          The argument name.
=>         Separates argument list from result expression.
x % 2 !=0  Returns true if x is not even.

Detailed examples. We take a closer look at lambdas and anonymous functions. The => operator separates the parameters to a method from its statements in the method’s body.

Tip:Lambda expressions use the token => in an expression context. In this context, the token is not a comparison operator.

Token

Goes to:The => operator can be read as “goes to.” It is always used when declaring a lambda expression.

Invoke:With Invoke, a method on Func and Action, we execute the methods in the lambdas.

C# program that uses lambda expressions

using System;

class Program
{
    static void Main()
    {
	//
	// Use implicitly typed lambda expression.
	// ... Assign it to a Func instance.
	//
	Func<int, int> func1 = x => x + 1;
	//
	// Use lambda expression with statement body.
	//
	Func<int, int> func2 = x => { return x + 1; };
	//
	// Use formal parameters with expression body.
	//
	Func<int, int> func3 = (int x) => x + 1;
	//
	// Use parameters with a statement body.
	//
	Func<int, int> func4 = (int x) => { return x + 1; };
	//
	// Use multiple parameters.
	//
	Func<int, int, int> func5 = (x, y) => x * y;
	//
	// Use no parameters in a lambda expression.
	//
	Action func6 = () => Console.WriteLine();
	//
	// Use delegate method expression.
	//
	Func<int, int> func7 = delegate(int x) { return x + 1; };
	//
	// Use delegate expression with no parameter list.
	//
	Func<int> func8 = delegate { return 1 + 1; };
	//
	// Invoke each of the lambda expressions and delegates we created.
	// ... The methods above are executed.
	//
	Console.WriteLine(func1.Invoke(1));
	Console.WriteLine(func2.Invoke(1));
	Console.WriteLine(func3.Invoke(1));
	Console.WriteLine(func4.Invoke(1));
	Console.WriteLine(func5.Invoke(2, 2));
	func6.Invoke();
	Console.WriteLine(func7.Invoke(1));
	Console.WriteLine(func8.Invoke());
    }
}

Output

2
2
2
2
4

2
2

A syntax review. Above we see many usages of lambda expressions. Sorry for the long example. The => operator separates arguments from methods. It does not compare numbers.

Left side:This is the parameters. It can be empty. Sometimes it can be implicit (derived from the right).

Right side:This is a statement list inside curly brackets with a return statement, or an expression.
Func1 through func8. Above, func1 through func8 denote anonymous function instances. The C# compiler often turns different syntax forms into the same code.
Func. The key part of Func is that it returns a value. It can have zero, or many, arguments. But its invariant is a return value, indicated by the TResult parametric type.Func

Func examples

Func<TResult>              Has one result value, no parameter.
Func<T, TResult>           Has one result value, one parameter.
Func<T1, T2, TResult>      Has one result value, two parameters.
Func<T1, T2, T3, TResult>  ....

Action. This class indicates a function that receives no parameter and returns no value. It matches a void method with no arguments. This guy is a solitary character.Action
Delegate. The delegate keyword denotes an anonymous function. After this keyword, we use a formal parameter list. We can omit the list if there are no parameters.Delegate
Anonymous functions. This term includes both delegates and lambda syntaxes. An anonymous function has no name. Perhaps it is running from the law.Anonymous Functions

Overloading:Because it has no name, method overloading is not possible for anonymous functions.

Overload

Note:Many developers regard lambda expressions as a complete improvement over (and replacement for) the delegate syntax.
Predicate. Here we use this type with an int parameter. With a lambda expression, we specify that the function returns true if the argument is equal to 5.Predicate

Invoke:In this program, the Invoke method is used to show that the Predicate works as expected.

C# program that uses Predicate

using System;

class Program
{
    static void Main()
    {
	Predicate<int> predicate = value => value == 5;
	Console.WriteLine(predicate.Invoke(4));
	Console.WriteLine(predicate.Invoke(5));
    }
}

Output

False
True

Comparison. This type is specifically used to compare objects. It is useful when calling the List.Sort or Array.Sort methods. It can be used with any object type.Comparison

Performance:Using methods such as List.Sort or Array.Sort (with a Comparison) is often faster than using LINQ to sort on a property.
Events. Like any other method, events can be specified as lambda expressions. With events, many event handlers are called when a certain thing happens. This can simplify some programs.Event
Performance. I benchmarked a lambda against an anonymous method, one using the delegate keyword. I used the functions as arguments to the Count() extension.

Result:I found no differences. The lambda expression performed the same as the explicit Func instance.

Thus:Lambda expressions cause no excess performance hit beyond other delegate syntaxes.

Locals used in benchmark: C#

int[] array = { 1 };
Func<int, bool> f = delegate(int x)
{
    return x == 1;
};

Lambda expression tested: C#

int c = array.Count(element => element == 1);

Delegate tested: C#

int c = array.Count(f);

Expression-bodied methods. A method can be specified with lambda expression syntax. We provide a method name, and the method is compiled like a lambda. A “return” statement is implicit.Return: Expression-Bodied
Expressive power. Lambdas advance a language. We can achieve the same thing with regular, non-lambda methods. But they make a language easier to use, more “expressive.”

Higher-order procedures can serve as powerful abstraction mechanisms, vastly increasing the expressive power of our language.

Structure and Interpretation of Computer Programs
Specification. The C# language specification describes anonymous function types. The annotated edition of The C# Programming Language (3rd Edition) covers all syntaxes available.

Tip:We can find more detail on this topic using the precise technical terminology on page 314 of this book.

Boring:This is pretty boring. Proceed at your own risk. Unless you are thinking about making a C# website, it may not be worth the effort.
Some help. Lambdas have unique syntactic rules. We had some help from the C# specification itself. We used lambdas with zero, one or many arguments, and with a return value.
Anonymous functions. These have no names, but we learned lots of their details. With the delegate keyword, we also specify method objects.