How to use LINQPad

 

Introduction

Querying is merely a special case of using LINQPad. More generally, LINQPad is a C#/VB/F# scratchpad that instantly executes any expression, statement block or program with rich output formatting and a wealth of features that "just work". Put an end to those hundreds of Visual Studio Console projects cluttering your source folder and join the revolution of LINQPad scripters, testers, and incremental developers. Reference your own assemblies and NuGet packages. Script in your favorite .NET language with optional autocompletion and the entire .NET Framework at your fingertips. Experience the magic of dynamic development and instant feedback in the ultimate programmer's playground!

Language-Integrated Query (LINQ) is a set of features introduced in Visual Studio that extends powerful query capabilities to the language syntax of C# and Visual Basic. LINQ introduces standard, easily-learned patterns for querying and updating data, and the technology can be extended to support potentially any kind of data store. Visual Studio includes LINQ provider assemblies that enable the use of LINQ with .NET Framework collections, SQL Server databases, ADO.NET Datasets, and XML documents.

You can download LINQPad from here http://www.linqpad.net/.

The steps we follow how to use LINQPad

Open the LINQPad and add new connection

Click on Add Connection then open the new connection wizard select Default (LINQ toSQL) and click the Next Button.

Enter the Server Name. Here we can mention the authentication type also

Specify New or existing Database

Select the Database name here

After that we can test the Connections

Now it’s showing all the database tables here

We can select the different Languages from here

Simple Select query and execute

Now we need to select the C# Statement(S)

Write a simple select query and for execute the query we need to use the .Dump() command.

After that we can run the query

Right click on the table we have get some more inbuilt query options

The Samples tab has more examples for efficient LINQ queries

Reference

http://www.linqpad.net/.

Create My First Entity Data Model(EDM)

 

This topic describes how to create a new .edmx file by using the Entity Data Model Wizard. An .edmx file contains the the conceptual model, as well as a storage model and the mappings between them. The procedures in this topic describe how to generate an .edmx file that is based on an existing database and how to generate an empty .edmx file.

Create new project in visual studio

Select ASP.NET Empty Web Application

(We can create Class library or console application)

Here Add new item in this project

Here we need to Add ADO.NET Entity Data Model

Now its open the Entity Data Model Wizard , we need to select option like Generate from database.

To create an empty .edmx file select the option Empty model.

Here we need to create New Connection

Select the option Microsoft SQL Server

Enter the Server name here

Select he Database name from the dropdown list.

Click on the Test Connection button ,it will show the Test Connection success message

Now the new connection created and we can see the entity connection string.

Here we have the options to select Tables,Views,Stored Procedures and Functions from that database.

Now our first .edmx file is generated

In our solution of the project we can see .edmx file which generated automatically

Reference

http://msdn.microsoft.com/en-us/library/cc716703(v=vs.103).aspx

Summary

In this article we learned the basic steps to create the EDM

Introduction to Entity Frame work

 

Overview

The Microsoft ADO.NET Entity Framework is an Object/Relational Mapping (ORM) framework that enables developers to work with relational data as domain-specific objects, eliminating the need for most of the data access plumbing code that developers usually need to write. Using the Entity Framework, developers issue queries using LINQ, then retrieve and manipulate data as strongly typed objects. The Entity Framework’s ORM implementation provides services like change tracking, identity resolution, lazy loading, and query translation so that developers can focus on their application-specific business logic rather than the data access fundamentals.

When working with the Entity Data Model Tools, the conceptual model, the storage model, and the mappings between the two are expressed in XML-based schemas and defined in files that have corresponding name extensions:

• Conceptual schema definition language (CSDL) defines the conceptual model. CSDL is the Entity Framework's implementation of the Entity Data Model. The file extension is .csdl.
• Store schema definition language (SSDL) defines the storage model, which is also called the logical model. The file extension is .ssdl.
• Mapping specification language (MSL) defines the mappings between the storage and conceptual models. The file extension is .msl.

The storage model and mappings can change as needed without requiring changes to the conceptual model, data classes, or application code. Because storage models are provider-specific, you can work with a consistent conceptual model across various data sources.

The Entity Framework uses these model and mapping files to create, read, update, and delete operations against entities and relationships in the conceptual model to equivalent operations in the data source. The Entity Framework even supports mapping entities in the conceptual model to stored procedures in the data source. For more information, see CSDL, SSDL, and MSL Specifications.

Accessing and Changing Entity Data

More than just another object-relational mapping solution, the Entity Framework is fundamentally about enabling applications to access and change data that is represented as entities and relationships in the conceptual model. The Entity Framework uses information in the model and mapping files to translate object queries against entity types represented in the conceptual model into data source-specific queries. Query results are materialized into objects that the Entity Framework manages. The Entity Framework provides the following ways to query a conceptual model and return objects:

• LINQ to Entities. Provides Language-Integrated Query (LINQ) support for querying entity types that are defined in a conceptual model. For more information, see LINQ to Entities.
• Entity SQL. A storage-independent dialect of SQL that works directly with entities in the conceptual model and that supports Entity Data Model concepts. Entity SQL is used both with object queries and queries that are executed by using the EntityClient provider. For more information, see Entity SQL Overview.

The Entity Framework includes the EntityClient data provider. This provider manages connections, translates entity queries into data source-specific queries, and returns a data reader that the Entity Framework uses to materialize entity data into objects. When object materialization is not required, the EntityClient provider can also be used like a standard ADO.NET data provider by enabling applications to execute Entity SQL queries and consume the returned read-only data reader. For more information, see EntityClient Provider for the Entity Framework.

The following diagram illustrates the Entity Framework architecture for accessing data:

Entity Framework vs. traditional ADO.Net

All of the standard ORM arguments apply here. The highlights are that you can write code against the Entity Framework and the system will automatically produce objects for you as well as track changes on those objects and simplify the process of updating the database. The EF can therefore replace a large chunk of code you would otherwise have to write and maintain yourself. Further, because the mapping between your objects and your database is specified declaratively instead of in code, if you need to change your database schema, you can minimize the impact on the code you have to modify in your applications--so the system provides a level of abstraction which helps isolate the app from the database. Finally, the queries and other operations you write into your code are specified in a syntax that is not specific to any particular database vendor--in ado.net prior to the EF, ado.net provided a common syntax for creating connections, executing queries and processing results, but there was no common language for the queries themselves; ado.net just passed a string from your program down to the provider without manipulating that string at all, and if you wanted to move an app from Oracle to SQL Server, you would have to change a number of the queries. With the EF, the queries are written in LINQ or Entity SQL and then translated at runtime by the providers to the particular back-end query syntax for that database.

Entity Framework vs. LINQ to SQL

The first big difference between the Entity Framework and LINQ to SQL is that the EF has a full provider model which means that as providers come online (and there are several in beta now and many which have committed to release within 3 months of the EF RTM), you will be able to use the EF against not only SQL Server and SQL CE but also Oracle, DB2, Informix, MySQL, Postgres, etc.

Next there is the fact that LINQ to SQL provides very limited mapping capabilities. For the most part L2S classes must be one-to-one with the database (with the exception of one form of inheritance where there is a single table for all of the entity types in a hierarchy and a discriminator column which indicates which type a particular row represents). In the case of the EF, there is a client-side view engine which can transform queries and updates made to the conceptual model into equivalent operations against the database. The mapping system will produce those views for a variety of transformations.

You can apply a variety of inheritance strategies: Assume you have an inheritance model with animal, dog:animal & cat:animal. You can not only do what L2S does and create a single table with all the properties from animal, dog & cat plus a column that indicates if a particular row is just a generic animal or a dog or a cat, but you can also have 3 tables where each table has all of the properties of that particular type (the dog table has not only dog-specific columns but also all the same columns as animal), or 3 tables such that the dog and cat tables have only the key plus those properties specific to their type of animal and retrieving a dog object would involve a join between the animal table and the dog table. And you can further combine these strategies so some parts of a hierarchy might live in one table and some parts in separate tables.

In addition you can do what we call "entity splitting" where a single type has properties which are drawn from two separate tables, and you can model complex types where there is a type which is nested within a larger entity and which doesn't have its own separate identity--it just groups some properties together. The best example of this is something like address where the street, city, state and zip properties go together logically, but they don't have independent identity. The address is only interesting as a set of properties that are part of a customer or whatever. As you have noticed, for v1 you can't create complex types with the designer in the EF--you have to code them by hand in the XML files.

References

http://blogs.msdn.com/b/dsimmons/archive/2008/05/17/why-use-the-entity-framework.aspx

http://msdn.microsoft.com/en-us/library/bb399567.aspx

Entity Framework Unit of Work and repositories

 

Overview

The Repository and Unit of Work (UoW) pattern are very useful patterns to create an abstraction level over the DbContext that is provided by Entity Framework. A much heard excuse not to use repositories is that EF itself already works with repositories (the DbContext) and a UoW (in the SaveChanges method). Below are a few examples why it is a good thing to create repositories:

• Abstract away some of the more complex features of Entity Framework that the end-developer should not be bothered with
• Hide the actual DbContext (make it internal) to prevent misuse
• Keep security checks and saving and rollback in a single location
• Force the use of the Specification pattern on queries

A Unit of Work (UoW) is a a combination of several actions that will be grouped into a transaction. This means that either all actions inside a UoW are committed or rolled back. The advantage of using a UoW is that multiple save actions to multiple repositories can be grouped as a unit.

A repository is a class or service responsible for providing objects and allowing end-developers to query data. Instead of querying the DbContext directly, the DbContext can be abstracted away to provide default queries and force required functionality to all end-developers of the DbContext.

A Unit of Work (UoW) is a a combination of several actions that will be grouped into a transaction. This means that either all actions inside a UoW are committed or rolled back. The advantage of using a UoW is that multiple save actions to multiple repositories can be grouped as a unit.

A repository is a class or service responsible for providing objects and allowing end-developers to query data. Instead of querying the DbContext directly, the DbContext can be abstracted away to provide default queries and force required functionality to all end-developers of the DbContext.

Creating a Unit of Work

A UoW can be created by simply instantiating it. The end-developer has the option to either inject the DbContext or let the DbContextManager take care of it automatically.

   1:  using (var uow = new UnitOfWork<MyDbContext>())

   2:  {

   3:      // get repositories and query away

   4:  }

Creating a repository

A repository can be created very easily by deriving from the EntityRepositoryBase class. Below is an example of a customer repository:

   1:  public class CustomerRepository : EntityRepositoryBase<Customer, int>, ICustomerRepository

   2:  {

   3:      public CustomerRepository(DbContext dbContext)

   4:          : base(dbContext)

   5:      {

   6:      }

   7:  }

   8:   

   9:  public interface ICustomerRepository : IEntityRepository<Customer, int>

  10:  {

  11:  }

Retrieving repositories from a Unit of Work

Once a UoW is created, it can be used to resolve repositories. To retrieve a repository from the UoW, the following conditions must be met:

1. The container must be registered in the ServiceLocator as Transient type. If the repository is declared as non-transient, it will be instantiated as new instance anyway.
   
2. The repository must have a constructor accepting a DbContext instance

To retrieve a new repository from the UoW, use the following code:

1: using (var uow = new UnitOfWork<MyDbContext>())

2: {

3: var customerRepository = uow.GetRepository<ICustomerRepository>();

4:

5: // all interaction with the customer repository is applied to the unit of work

6: }

Saving a Unit of Work

It is very important to save a Unit of Work. Once the Unit of Work gets out of scope (outside the using), all changes will be discarded if not explicitly saved.

1: using (var uow = new UnitOfWork<MyDbContext>())

2: {

3: var customerRepository = uow.GetRepository<ICustomerRepository>();

4:

5: // all interaction with the customer repository is applied to the unit of work

6:

7: uow.SaveChanges();

8: }

References

http://blogs.msdn.com/b/adonet/archive/2009/06/16/using-repository-and-unit-of-work-patterns-with-entity-framework-4-0.aspx

http://blog.catenalogic.com/post/2013/02/27/Entity-Framework-Unit-of-Work-and-repositories.aspx

Summary

The repository and unit of work patterns are both very powerful if implemented correctly. They provide a lot of flexibility, extensibility and lower the development and testing friction.