LINQ Zip and join

Add two list sequentially together according to it order... stop when end of one list.

Fast but limited compare with join.

(roleList).Zip(roleDescriptionList, (r, rd) => new RoleDescription() { roleID = r.dataID, role = r.dataValue, description = rd.dataValue }).ToList();

roleList and roleDescriptionList are two separate lists.  They can be unrelated at all.
Using Zip() to join them together to create a new list of object "RoleDescription" one by one.

To have better control, you should use join instead.


IList<RoleDescription> result = (from r in roleList
                         join rd in roleDescriptionList
                         on r.dataID equals rd.dataID
                         select new RoleDescription { 
                             roleID = r.dataID, 
                             role = r.dataValue, 
                             description = rd.dataValue }).ToList();

LINQ to Objects

LINQ to Collections...

"The term 'LINQ to Objects' refers to the use of LINQ queries with any IEnumerable or IEnumerable(Of T) collection directly, without the use of an intermediate LINQ provider or API."

LINQ to SQL

LINQ to SQL makes database programming a direct part of the VB.NET/C#  language. Before it was added to the language toolkit with Framework 3.5/VB.NET 2008, databases were a world apart from your application programming code and you had to get your data through an interface. Coding for SQL Server this way doesn't take advantage of some of the best features of VB.NET/C# development such as the use of  Intellisense. (Using older methods, the tables and fields in the database are unknown to the VB.NET code so Visual Studio can't tell you whether your names are right or wrong. And the compiler can't tell you whether your SQL commands are correct because they're passed directly to SQL Server. Your code has to be able to interpret whatever comes back from SQL Server.)

There are basically two problems to be solved when accessing a SQL Server database from your application:

1. Data in a database doesn't have the same "types" as data in .NET. This is often called the database "impedance mismatch".  The process of converting types from a database into types in an application is called object relational mapping (ORM).  Today, LINQ to SQL provides a new tool in Visual Studio, the Object Relational Designer, and just takes care of the rest of the problem.
2. SQL statements have to be passed to the database engine for execution and results (or errors) returned to the application. The SqlClient namespace in ADO.NET provides the ExecuteReader, ExecuteNonQuery, and ExecuteScalar methods to run queries. Part of the problem is that it's not always clear which one to use. LINQ to SQL gives you VB.NET/C# statements that you can compile in your application and eliminates the need to use these methods. The new LINQ to SQL syntax looks a lot like traditional SQL too.

Create SQL to SQL:

1. Add database connection
2. Add DataContext (LINQ to SQL Classes) (".dbml")

The bottom line is that LINQ to SQL is a lightweight technology that can make your SQL Server coding faster and easier. It's a good choice for most projects, but if you need the best performance or if your application has complex requirements, you might want to go the traditional route or consider the ADO.NET Entity Framework.

Relational Data Model            LINQ to SQL Object Model 

----------------------------     -------------------------------

Table                            Entity class

Column                           Class member 

Foreign-key relationship         Association 

Stored Procedure or Function     Method

Entity Framework

The Entity Framework provides a completely independent "conceptual model" of the data. When you use the Entity Framework, you code queries against this conceptual model and not against the actual data store. Entity Framework has its own dialect of SQL used to retrieve data called Entity SQL. It's not SQL Server's TSQL query language. In fact, the data store does not have to be a database.

The conceptual model contains what Microsoft calls "entities". The Entity Framework adds a layer of software to your application that maps the data from a data store to these entities. Of course, the data store still has to provide the data so the Entity Framework "conceptual model" has to be created and configured before it will work in your code.

LINQ to SQL gives you the ability to access a SQL Server data store using VB.NET/C# language elements.
LINQ to Entities gives you the abilty to access the Entity Framework model, which could potentially include data from any data store, using VB.NET/C# language elements.

Entity Framework can provide the many-to-many relationship in the data model automatically.

Install "Microsoft Visual Studio 2010 ADO.NET Entity Framework Tools"
Create the Entity Framework conceptual model
Right click on the project, select Add, New Item..., Data, ADO.NET Entity Data Model


Microsoft gives you two ways to access the data in the Entity Framework model:

• LINQ to Entities
• Entity SQL (A variant of the venerable SQL language just for the Entity Framework)

Since Entity Framework implements both IQueryable (used more in Entity Framework) and IEnumerable (used in LINQ), you actually get the advantages of both worlds.

more info...

LINQ

"Language INtegrated Query"

LINQ can be used to create and filter data into arrays, enumerable classes, XML, relational databases, and other data stores. You can even use LINQ query expressions as the basis for statements like building event handlers. This is one of the reasons LINQ can be confusing.

    From ...
    Where ...
    Select ...

LINQ adds the abililty to use the powerful query syntax.  LINQ is "language level" querying.

more info...

Technologies access Data in Microsoft world

ADO.NET

• works directly through an API (program calls to an interface) that calls the internal functions of the database or other datastore
• for maximum performance

LINQ to SQL
LINQ to DataSet
LINQ to Entities
Entity Framework

all use ADO.Net as their foundation