Entity Class Serialization

Entity Class Serialization

Object serialization to a data transport format that can pass between processes of distributed systems is

required to use EF in n - tier, service - oriented architecture (SOA). Object serialization also is required to

save object state in ViewState between postbacks of ASP.NET Web pages. .NET 3.5 supports the

following types of serializers:

1. .NET BinarySerializer , which is used to store objects in the ASP.NET Page. ViewState

property, can save the entity and its related objects on postback, then retrieve the objects during

the succeeding page load and attach them to a newly created ObjectContext . The

BinarySerializer also is used for .NET Remoting.

2. .NET XmlSerializer for SOAP Web services prior to the release of WCF with .NET 3.0. The

XmlSerializer can ’ t serialize relationships, which makes it useless for serializing

EntityType s with relationships.

3. WCF DataContractSerializer ( DCS ) for .NET 3.5 and VS 2008 services. The original DCS

version released with .NET 3.5 and VS 2008 could not serialize objects with both many:one and

one:many associations because the combination creates a cyclic relationship or cycle . A relatively

minor change in .NET 3.5 Service Pack (SP) 1 enables DCS to handle potential cycles and serialize

entire object graphs by a DataContractAttribute.IsReference=true attribute value. This

feature eliminates the need to write complex hacks to pass EntityType s with both types of

associations between tiers.

4. WCF DataContractJsonSerializer ( DCJS ) for .NET 3.5 and VS 2008 AJAX services, which

has limitations similar to the XmlSerializer . DCJS isn ’ t intended for use with EntityType s.

Using the DCJS with EntityType s throws an error due to addition of namespace prefixes to

references added by the DataContractAttribute.IsReference=true attribute value.

LINQ to SQL v1 offers serialization of only one:many (unidirectional) associations, so it doesn ’ t

preserve EDM object graphs in which any associations are bidirectional.

Entity Framework Futures

Entity Framework Futures

Tim Mallalieu ’ s “ Entity Framework Futures ” session for the Microsoft Professional Developers

Conference (PDC) 2008 outlined in more detail what developers can expect from EF v2 and other future

Entity Data Model implementations. (You can view the Channel9 video of the presentation at http://

channel9.msdn.com/pdc2008/TL20/ ).

Mallalieu emphasized that data access is the first EDM implementation, but the Entity Data Platform

(EDP) will ultimately manage the following domains:

• ·         Data access and object persistence (EF)
• ·         Integration, aggregation, and synchronization
• ·         Reporting and analytics
• ·         Data management, deployment, policy, and security
• ·         Models and workflows

These scenarios will be supported by four primary EDP technology categories:

• ·         Client technologies
• ·         Mid - tier frameworks and technologies
• ·         Data access APIs and frameworks
• ·         Core storage

Data consumers fall into the following segments, each with their own needs in the preceding domains

and technologies:

• ·         Traditional data developers
• ·         Developers using scripting and “ rebel frameworks, ” such as agile programming
• ·         Model - centric data developers
• ·         Line of business (LOB) script or macro writers
• ·         Information workers

To satisfy agile programmers who insist on plain old CLR objects (POCOs) and total persistence

ignorance, Mallalieu demonstrated writing class code for POCOs and an ObjectContext in the simplest

possible, convention - oriented way. This approach eliminates the EDM and its XML files, and defines an

ObjectSet that implements Iqueryable to deliver completely persistence ignorant code. Adding

mapping attributes enables configuration - oriented code to specify mapping to tables in other schemas or

with column names that don ’ t correspond to property names.

Model - centric developers want to define a model with the EDM Designer and then generate the

database from the model in a manner similar to that employed by LINQ to SQL. EF v2 will add this

capability, which (unfortunately) destroys all data in the database, as well as the capability to define

complex types and implement them by a new “ Artifact Generation Pipeline ” that combines Windows

Workflow and T4 templates. This feature enables developers to modify the workflow, customize the

templates, and build their own design tooling.

Framework developers are independent software vendors (ISVs) who want to build out a data access

layer that leverages the EDM. For example a DBA might create the EDM and the framework developer

would use the *.edmx model to generate POCO classes and enable lazy loading, which was missing from

v1. To prevent the need for direct access to database tables, EF v2 will enable SQL Server table - valued

functions as a data source for composable LINQ to SQL queries, which stored procedures don ’ t support.

New model - defined functions (MDFs) let developers add function definitions, such as FullName() , to

the model. You define MDFs in code, add the SQL expression in the *.edmx file; the expression tree

executes the expression in - line.

Finally, a new feature nicknamed “ Slambda ” lets you use a DbExpression or comprehension syntax to

build a query tree which returns a ADO.NET DbDataReader instead of a typed collection. Beyond v2,

EF and EDM will become a part of the forthcoming Oslo modeling framework with an MEntity domain -

specific grammar. Missing from the presentation was an example of n - tier deployment of EF without the

added overhead of an ADO.NET Data Services (Astoria) layer, as well as support for Contains and

enums, which the DP team won ’ t commit to including in v2.

Entity Framework vs. LINQ to SQL

 Entity Framework vs. LINQ to SQL
Offering two competing O/RM tools with different feature sets to .NET developers is difficult to justify
as an ongoing business strategy, and the DP ’ s team ’ s large - scale investment in and grandiose plans for
EF made orphaning of LINQ to SQL inevitable.
Tim Mallalieu, program manager for LINQ to SQL and Entity Framework, posted the “ Update on LINQ
to SQL and LINQ to Entities Roadmap ” article to the ADO.NET Team Blog on October 29, 2008
( http://blogs.msdn.com/adonet/archive/2008/10/29/update - on - linq - to - sql - and - linq -
to - entities - roadmap.aspx ). Following is the article ’ s last paragraph:
We ’ re making significant investments in the Entity Framework such that as of .NET 4.0 the
Entity Framework will be our recommended data access solution for LINQ to relational scenarios.
We are listening to customers regarding LINQ to SQL and will continue to evolve the product
based on feedback we receive from the community as well.
Most authors of data - intensive VS 2008 project examples — such as Scott Guthrie, corporate VP of the
.NET Developer Division — used LINQ to SQL as the preferred data source. Guthrie wrote a detailed,
nine - part LINQ to SQL tutorial targeted to ASP.NET and Web developers in 2007 ( http://weblogs
.asp.net/scottgu/archive/2007/09/07/linq - to - sql - part - 9 - using - a - custom - linq -
expression - with - the - lt - asp - linqdatasource - gt - control.aspx ). Sample ASP.NET Model -
View - Controller (MVC) and ASP.NET Dynamic Design projects used LINQ to SQL rather than EF as
their data source. This isn ’ t surprising when you consider that LINQ to SQL was a fully qualified
component of .NET 3.5 and EF didn ’ t arrive until .NET 3.5 and VS 2008 SP1.
Many developers had made significant investments in applying LINQ to SQL to new .NET 3.5 projects
before EF was released. Scott Hanselman, Microsoft ’ s principal program manager for community liaison,
released in October 2008 an “ Informal .NET Subsystem Survey ” that displayed the number of developers
using each of 14 .NET 3.5 and VS 2008 SP1 features ( http://www.hanselman.com/blog/
SurveyRESULTSWhatNETFrameworkFeaturesDoYouUse.aspx ). Of 4,899 respondents in a one - week period, 1,734 (35.5%) reported using LINQ to SQL versus 643 (13.1%) using EF. Use of LINQ to SQL was
almost neck - and - neck with ADO.NET DataSets at 1,887 users (38.5%).
Therefore, it ’ s not surprising that Mallalieu ’ s initial blog post engendered an outcry from the .NET
developer community. An extraordinary number of apocalyptic “ Death of LINQ to SQL ” or similarly
titled posts appeared on developer - oriented blogs. Rumors of LINQ to SQL ’ s death are premature,
because its assemblies are part of the .NET 3.5 [SP1] System.Data namespace and must be retained ad
infinitum for backward compatibility.
Mallalieu attempted to defend the DP group ’ s position in an October 31, 2008 “ Clarifying the message
on L2S Features ” post ( http://blogs.msdn.com/adonet/archive/2008/10/31/clarifying - the -
message - on - l2s - futures.aspx ) attempted to deflect the uproar by asking the rhetorical question,
“ Is LINQ dead? ” He then asserted that “ we decided to take the EF forward with regards to the overall
convergence effort and over time provide a single solution that can address the various asks ” and
confirmed EF ’ s status as the “ recommended data access solution for LINQ to relational scenarios. ”
The upshot of the DP group ’ s decision is that LINQ to SQL will enter the purgatory of deprecated
technologies upon the release of .NET 4.0 and VS 2010. Microsoft - supported improvements to LINQ to
SQL will be few and far between until then and non - existent thereafter. This means developers should
place bets on EF for data - intensive projects other than those for short - term, ad hoc individual or
departmental application.

Microsoft SQL Server 2012 RC 0

SQL SERVER 2012

Microsoft SQL Server Overview

Microsoft SQL Server 2012 is a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization and quickly build solutions to extend data across on-premises and public cloud, backed by mission critical confidence.

http://www.microsoft.com/sqlserver/en/us/product-info/overview-capabilities.aspx

Uniqueness of nullable field

SQL SERVER Uniqueness of nullable field

Assuming you have the table with nullable field. What if you need to support uniqueness on not-null values only? For example, you can have the customers table with optional SSNs - not all customers provide it but if SSN is provided, you want to make sure it’s unique.

Regular index is not an option - SQL Server would not allow you to have more than 1 null value.

SQL Server 2008 provides great feature - filtered index - you will be able to create it on not null fields only.

Now when you insert duplicate value - it fails:

With SQL 2005 it’s not an option. Although you can use the indexed view:

Now when you insert the duplicate, it violates clustered index on the view and fails

The power of T-SQL's APPLY operator

APPLY, in both its inner (CROSS APPLY) and outer (OUTER APPLY) forms, allows a correlated sub-query or table-valued function to be part of the FROM clause. This is different to a regular JOIN, which is between two sets that are independent of each other, but able to be joined according to some predicate that lives in the ON clause. Using APPLY, that restriction disappears, and the predicate can be built into the sub-query (or TVF) itself.
These two queries are exactly the same. They have identical execution plans, and produce the same result.

SELECT p.Name, s.Name, s.ProductCategoryID
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON s.ProductSubcategoryID = p.ProductSubcategoryID;

SELECT p.Name, s.Name, s.ProductCategoryID
FROM Production.Product AS p
CROSS APPLY (
SELECT subcat.Name, subcat.ProductCategoryID
FROM Production.ProductSubcategory AS subcat
WHERE subcat.ProductSubcategoryID = p.ProductSubcategoryID) AS s;

You may look at this and figure that the first option is entirely adequate, and see no reason to switch. There isn’t a reason to switch for most situations, and I don’t actually want you to change the way you write regular joins. But I do want you to consider the power of having a predicate as part of a correlated table-sub-query.
One common scenario is being able to hook easily into the first row in a particular join. It’s easy – you just add TOP (1) to the sub-query, and put an ORDER BY clause in there. I do this happily in BI solutions, where I want to grab a record from a dimension table that is current at the time that an event occurred.

OUTER APPLY (
SELECT TOP (1) d.DimKey
FROM dbo.DimensionTable AS d
WHERE d.BusinessKey = e.DimensionCode
AND d.StartDate < e.EventDate
ORDER BY d.StartDate DESC) AS d

I know, you can do this very easily using a correlated sub-query in the SELECT clause, since the sub-query returns a single value (yes, it’s scalar). But what if we needed to return multiple columns from a sub-query, or needed to return the best three records for a particular category. Here I’m finding the three best-selling products for each subcategory.

SELECT s.Name, TopProds.*
FROM Production.ProductSubcategory AS s
OUTER APPLY (
SELECT TOP (3) p.Name, SUM(o.OrderQty) AS NumOrdered
FROM Production.Product AS p
JOIN Sales.SalesOrderDetail AS o
ON o.ProductID = p.ProductID
WHERE p.ProductSubcategoryID = s.ProductSubcategoryID
GROUP BY p.ProductID, p.Name
ORDER BY SUM(o.OrderQty) DESC) as TopProds
;

This could be done with a Common Table Expression (CTE) and the ROW_NUMBER() functdion, but that solution isn’t nearly as elegant as this. This is the kind of solution that was designed for APPLY. One of those times when a problem and solution are nicely matched, made for each other.
Here’s an example that achieves the same using a CTE:

WITH OrderedProds AS
(
SELECT p.ProductSubcategoryID,
p.Name,
SUM(o.OrderQty) AS NumOrdered,
ROW_NUMBER() OVER (PARTITION BY p.ProductSubcategoryID
ORDER BY SUM(o.OrderQty) DESC) AS ProdOrder
FROM Production.Product AS p
JOIN Sales.SalesOrderDetail AS o
ON o.ProductID = p.ProductID
GROUP BY p.ProductID, p.Name, p.ProductSubcategoryID
)
SELECT s.Name, o.Name, o.NumOrdered
FROM Production.ProductSubcategory s
LEFT JOIN OrderedProds o
ON o.ProductSubcategoryID = s.ProductSubcategoryID
WHERE o.ProdOrder <= 3
;

I’m sure you’ll appreciate that this feels a lot more contrived than using APPLY. The Query Optimizer should really break these two queries down to exactly the same, recognising that joining on the PARTITION BY column(s) is a correlation mechanism. It doesn’t do that yet, but one day, I’m sure it might.
(The CTE query runs quicker than the APPLY equivalent in this particular case on the AdventureWorks database, but it shouldn’t need to. The behaviour largely depends on a number of other factors, in the same way that multiple Seeks can sometimes be faster than a Scan, but not always.)
Let’s think about views for a moment. They’re sometimes referred to as “Virtual Tables” (yes, SQL Books Online, I’m looking at you!)
People who have heard my preaching presenting about writing good T-SQL will have heard me refer to views as “stored sub-queries”. I reject the notion that they’re “virtual tables”. That’s rubbish – they’re treated in by the Query Optimizer as a stored sub-query, and should be referred to as such. The natural extension of this is that an inline table-valued function is a parameterised view, or stored correlated table sub-query. Basically the same as what we see here.
So let’s create it.

CREATE FUNCTION dbo.Top3ProductsBySubcategory(@subcat int) RETURNS TABLE
AS RETURN(
SELECT TOP (3) p.Name, SUM(o.OrderQty) AS NumOrdered
FROM Production.Product AS p
JOIN Sales.SalesOrderDetail AS o
ON o.ProductID = p.ProductID
WHERE p.ProductSubcategoryID = @subcat
GROUP BY p.ProductID, p.Name
ORDER BY SUM(o.OrderQty) DESC
);

Notice I’ve simply changed the correlation hook (s.ProductSubcategoryID) into the parameter. Also note that I’m NOT using BEGIN and END, which would render the function procedural and non-simplifiable. To hear me go on about that in more detail, have a look at the video at http://bit.ly/SimpleRob – a URL I have to thank Farmer Paul for, which will direct you to a presentation I did at SQLBits V, in late 2009. (I figure what Paul meant when he created “Simple Rob” is that I present simple truths that have a profound importance, and not suggesting that I’m ‘simple’ at all. He’s not suggesting that my IQ is low or anything like that. He wouldn’t do that, he’s far too nice a bloke, and my friend. I mean, I know he’s a Kiwi, and Kiwis have a friendly rivalry with Aussies – I think they’re jealous of our weather – but he wouldn’t pick on me like that. Besides, Paul and I are both English originally, he just moved to NZ instead of Australia. Maybe he just enjoys colder weather.)
So now my query can become:

SELECT s.Name, TopProds.*
FROM Production.ProductSubcategory AS s
OUTER APPLY dbo.Top3ProductsBySubcategory(s.ProductSubcategoryID) AS TopProds
;

This executes in exactly the same way, but we’ve contained our logic nicely by storing the sub-query in an inline TVF. Notice the join condition – it’s buried inside the function, abstracted away, and yet completely simplifiable by the Query Optimizer. It’s still very much a join – if you were to look at the execution plan, you’d see a Nested Loop (Left Outer Join), with a Seek Predicate being used further down. (I’ve created indexes to help, of course.) You should be able to click on the image to see it in more detail.

I really could keep going about APPLY, and talk about a bunch of other uses for it, but I’ll leave that for another time.
What I want to leave you with is the reminder that APPLY is just a more powerful way of doing a JOIN, because the set that you’re joining to doesn’t need to exist yet. You may have heard people talk about APPLY being bad, in the same breath as people will tell you that a correlated sub-query is bad. Unfortunately, there are times when this is true. But there are times when any join is bad, it largely depends on what indexes you have in place, and what kind of simplification the Query Optimizer can do in working out your plan. If you have considered issues such as SARGability and Residual Predicates, then you really shouldn’t have much of a problem.
Of course you understand joining to Tables, and to Views. If you aren’t used to joining to sub-queries, then try expanding a view yourself. Then think about what happens if you use APPLY and sneak the join condition into a predicate within the sub-query. All of a sudden you’ll find yourself able to write richer queries, and have a much stronger T-SQL arsenal.

------------------------------------------------------------------------------------------
The article shows how to write a function that returns the top three countries per category. That's always been something that was difficult to do in SQL so I was curious about the approach. The article started out well but I was very unhappy by the end. It's just soooo much easier to do this in SQL Server 2005 using the new CROSS APPLY clause in Transact-SQL. So I'm going to write a query to return the top 3 orders for each customer and I'm going to do it in about 10 lines of SQL. (UPDATE: An alert reader found an even better approach!)

First please take a second to read the MSDN article. Pay special attention to how much C# code is required for the solution and how flexible it is.Note: I also want to mention here that I started out trying to replicate their solution. I tried to install the MSI but that failed with an invalid characters message. I tried to recreate the CLR code but since he mostly posted snippets and had three versions I finally gave up trying to get that to work. One thing I really wanted to do was compare performance on identical data. Unfortunately I wasn't able to do that. My example will use the AdventureWorks database

Let's start at the TOP

Along the way to making this solution work we'll discuss a few new features of SQL Server 2005. The first is that the TOP clause can now take a variable:

DECLARE @Rows INT
SET @Rows = 10

SELECT TOP ( @Rows ) *
FROM Sales.SalesOrderHeader

This will return the top 10 rows from SalesOrderHeader. You can also replace @Rows with anything that evaluates to a number. The following query looks odd but runs just fine:

SELECT TOP ( 
 SELECT COUNT(*)
 FROM Sales.Customer
   ) *
FROM Sales.SalesOrderHeader

There are 19,185 rows in the Customer table and this query returns the top 19,185 rows from SalesOrderHeader. You can also use the TOP clause for INSERT, UPDATE and DELETE statements. If you wanted to DELETE in batches of 500 you can now do that using the TOP clause.

My Function

Next we need a function to return the TOP X rows from SalesOrderHeader based on the total sales amount. That function looks like this:

CREATE FUNCTION dbo.fn_GetTopOrders(@custid AS int, @n AS INT)
  RETURNS TABLE
AS
RETURN
  SELECT TOP(@n) *
  FROM Sales.SalesOrderHeader
  WHERE CustomerID = @custid
  ORDER BY TotalDue DESC
GO

Notice that it accepts the number of orders to return as a parameter. Also notice that I'm using SELECT * all over the place. I really encourage you to explicitly list out the columns when you write this for real. If you call this with a CustomerID and a number of rows it will return that many rows ordered by the total amount of the order in descending order. Also notice that there is an ORDER BY clause in this function. We'll talk about that in a minute.

Apply the APPLY Clause

The real magic happens when you use SQL Server 2005's new APPLY clause. The APPLY clause let's you join a table to a table-valued-function. That let's you write a query like this:

SELECT  C.CustomerID, 
 O.SalesOrderID,
 O.TotalDue
FROM 
 AdventureWorks.Sales.Customer AS C
CROSS APPLY
 AdventureWorks.dbo.fn_GetTopOrders(C.CustomerID, 3) AS O
ORDER BY 
 CustomerID ASC, TotalDue DESC

which results in this...

CustomerID  SalesOrderID TotalDue
----------- ------------ ---------------------
1           45283        37643.1378
1           46042        34722.9906
1           44501        26128.8674
2           46976        10184.0774
2           47997        5469.5941
2           57044        4537.8484
3           53616        92196.9738
3           47439        78578.9054
3           48378        56574.3871
4           47658        132199.8023
. . .

The APPLY clause acts like a JOIN without the ON clause comes in two flavors: CROSS and OUTER. The OUTER APPLY clause returns all the rows on the left side (Customers) whether they return any rows in the table-valued-function or not. The columns that the table-valued-function returns are null if no rows are returned. The CROSS APPLY only returns rows from the left side (Customers) if the table-valued-function returns rows.
Notice that I'm just passing in the CustomerID to the function. It returns the TOP 3 rows based on the amount of the order. Since I'm using CROSS APPLY a customer without orders won't appear in the list. I can also pass in a number other than 3 to easily return a different number of orders per customer. Even better I can pass in a different number of orders for each customer. So I could list the top 5 orders for one type of customer but the top 10 for another type of customer. How cool is that?!?
And it gets even better. Remember the function has an ORDER BY in it. It will always return the top orders based on the amount. However you can change the ORDER BY clause in the query that calls the function to display those rows in whatever order you want. You could easily display the top 3 orders in ascending order instead.

Performance and Conclusion

Unfortunately I wasn't able to compare the performance of my solution to MSDN's. The query plan from my solution doesn't look that bad. (And did I mention that it showed me a full query plan for the query with the UDF rather than just a UDF step? Sweet!) If anyone gets their solution running send me the code and I'll update the article.
I also think their solution returns the result as a comma separated value. Now that would be a great use for the CLR. You could easily wrap that around the results of this function for a fast, effecient procedure. And it happens to be the next article I'm working on. :) If you want to see a great article on using the CLR inside SQL Server I'd start with Using CLR Integration in SQL Server 2005. The code is from an earlier beta but it gives you a great idea of when to use CLR code.
In summary I think that 5-10 lines of SQL is much simpler than 50+ lines of C# (or VB) for this type of solution. The article provides a lot of good information on the CLR inside SQL Server. I just don't think this is the best place to use it. One of my biggest fears is that people will use the CLR incorrectly and cause performance problems. This is the first real case I've seen of this. Is this really how Microsoft wants us to use the CLR?

UPDATE

I was curious when I posted this how long it would take to find an even better solution. It didn't take long. Arnold Fribble posted it in the forums. The syntax needed a little tweaking but it appears you can do this in a single query. And how did I forget the PARTITION BY clause of the ROW_NUMBER function. Doh! Here's the cleaned up query:

SELECT 
 CustomerID,
 SalesOrderID,
 TotalDue
FROM (
 SELECT
  O.CustomerID,
  O.SalesOrderID, 
  O.TotalDue, 
  ROW_NUMBER = ROW_NUMBER() OVER (
   PARTITION BY O.CustomerID 
   ORDER BY O.TotalDue DESC)
 FROM 
  Sales.SalesOrderHeader AS O
 ) AS d
WHERE
 d.ROW_NUMBER <= 3

You have to use a derived table (or a Common Table Expression) since the row number function can't be used in the WHERE clause of the query where it's defined. Now I'll have to write an article on the new ranking functions.

see also
http://www.developer.com/db/article.php/3798361/Using-T-SQL-CROSS-APPLY-and-OUTER-APPLY.htm

SQL SERVER TSQL Repeatable Read isolation level

The Repeatable Read isolation level allows a transaction to acquire read locks on all rows of data it returns to an application,
and write locks on all rows of data it inserts, updates, or deletes.

By using the Repeatable Read isolation level, SELECT SQL statements issued multiple times within the same transaction will always yield the same result.
 A transaction using the Repeatable Read isolation level can retrieve and manipulate the same rows of data as many times as needed until it completes its task.

However, no other transaction can insert, update, or delete a row of data that would affect the result table being accessed, until the isolating transaction releases its locks. That is, when the isolating transaction is either committed or rolled back.

Transactions using the Repeatable Read isolation level wait until rows of data that are write-locked by other transactions are unlocked before they acquire their own locks.


This prevents them from reading "dirty" data. In addition, because other transactions cannot update or delete rows of data that are locked by a transaction using the Repeatable Read isolation level, nonrepeatable read situations are avoided.