пятница, 28 сентября 2012 г.

Generate INSERT statements for a SQL Server table?


Microsoft should advertise this functionality of SSMS 2008. The feature you are looking for is built into the Generate Script utility, but the functionality is turned off by default and must be enabled when scripting a table.
This is a quick run through to generate Insert statements for all of the data in your table, using no scripts or add-ins to SQL Management Studio 2008:
  1. Right click on the database and go to Tasks -> Generate Scripts
  2. Select the tables (or object) that you want to generate the script against.
  3. Go to Set scripting options and click on the Advanced button.
  4. In the General category, go to Type of data to script
  5. There are 3 options: Schema OnlyData Only, and Schema and Data. Select the appropriate option and click on OK.
You will then get the CREATE TABLE statement and all of the INSERT statements for the data straight out of SSMS.

Method not found: 'Void System.Data.Objects.ObjectContextOptions.set_UseConsistentNullReferenceBehavior(Boolean)'.


Method not found: 'Void System.Data.Objects.ObjectContextOptions.set_UseConsistentNullReferenceBehavior(Boolean)'.
Looks like you are using the EF5 EntityFramework.dll but then running with .NET 4 installed instead of .NET 4.5.
If you are intending to target .NET 4.5, then make sure that .NET 4.5 is installed on the machine on which you are running the code. VS11 installs .NET 4.5 for you, but you can also install just the runtime on a non-dev machine.
If you are intending to target .NET 4, then make sure that you project is set to target .NET 4 and then uninstall and reinstall the EF5 NuGet package. You must uninstall and reinstall because NuGet won't change the assembly in use automatically when the project is re-taregted.
Update:
You may also see this exception if you still have the EF June CTP installed or if it failed to uninstall correctly. Please try to uninstall the June CTP fully before installing VS11. If you still see problems them open a "Developer Command Prompt" as Administrator and run:
gacutil /u System.Data.Entity,Version=4.2.0.0,Culture=neutral,PublicKeyToken=b77a5c561934e089
If this still fails, then you can attempt to force the uninstall from the GAC with:
gacutil /uf System.Data.Entity,Version=4.2.0.0,Culture=neutral,PublicKeyToken=b77a

EF Code First - One-to-One Primary Key Associations

In the previous blog post I demonstrated how to map a special kind of one-to-one association—a composition with complex types as the first post in a series about entity association mapping with EF Code First. We argued that the relationships between User and Address are best represented with a complex type mapping and we saw that this is usually the simplest way to represent one-to-one relationships but comes with some limitations.

In today’s blog post I’m going to discuss how we can address those limitations by changing our mapping strategy. This is particularly useful for scenarios that we want a dedicated table for Address, so that we can map both User and Address as entities. One benefit of this model is the possibility for shared references— another entity class (let’s say Shipment) can also have a reference to a particular Address instance. If a User has a reference to this instance, as her BillingAddress, the Address instance has to support shared references and needs its own identity. In this case, User and Address classes have a true one-to-one association.

Introducing the Revised Model

In this revised version, each User could have one BillingAddress (Billing Association). Also Shipment has to be delivered to an address so it always has one Delivery Address (Delivery Association). Here is the class diagram for this domain model (note the multiplicities on association lines):
In this model we assumed that the billing address of the user is the same as her delivery address. Now let’s create the association mappings for this domain model. There are several choices, the first being a One-to-One Primary Key Association.

Shared Primary Associations

Also know as One-to-One Primary Key Associations, means two related tables share the same primary key values. The primary key of one table is also a foreign key of the other. Let’s see how we map the primary key associations with Code First.

How to Implement a One-to-One Primary Key Association with Code First

First, we start with the POCO classes. As you can see, we've defined BillingAddress as a navigation property on User class and another one on Shipment class named DeliveryAddress. Both associations are unidirectional since we didn't define related navigation properties on Address class as for User and Shipment.
public class User
{
    public int UserId { getset; }
    public string FirstName { getset; }
    public string LastName { getset; }
    public virtual Address BillingAddress { getset; }
}
 
public class Address
{
    public int AddressId { getset; }
    public string Street { getset; }
    public string City { getset; }
    public string PostalCode { getset; }
}
 
public class Shipment
{
    public int ShipmentId { getset; }
    public DateTime CreatedOn { getset; }
    public string State { getset; }
    public virtual Address DeliveryAddress { getset; }
}
 
public class EntityMappingContext : DbContext
{
    public DbSet<User> Users { getset; }
    public DbSet<Address> Addresses { getset; }
    public DbSet<Shipment> Shipments { getset; }
}

How Code First Reads This Object Model: One-to-Many

Code First reads the model and tries to figure out the multiplicity of the associations. Since the associations are unidirectional, Code First takes this as if one Address has many Users and Many Shipments and will create a one-to-many association for each of them. So, what we were hoping for —a one-to-one association, is not inline with the conventions.

How to Change the Multiplicity to One-to-One by Using the Conventions

One way to turn our associations to be one-to-one is by making them bidirectional. That is, adding a new navigation property to Address class of type User and another one of type Shipment. By doing that we basically signal Code First that we are looking to have one-to-one associations since for example User has an Address and also Address has a User. Based on the conventions, Code First will change the multiplicity to one-to-one and this will solve the problem.

Should We Make This Association Bidirectional?

As always, the decision is up to us and depends on whether we need to navigate through our objects in that direction in the application code. In this case, we’d probably conclude that the bidirectional association doesn’t make much sense. If we call anAddress.User, we are saying “give me the user who has this address”, not a very reasonable request. So this is not a good option. Instead we'll keep our object model as it is and will resort to fluent API.

How to Change the Multiplicity to One-to-One with Fluent API

The following code is all that is needed to make the associations to be one-to-one. Note how the multiplicities in the UML class diagram (e.g. 1 on User and 0..1 on address) has been translated to the flunet API code by using HasRequired and HasOptional methods:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>().HasOptional(u => u.BillingAddress)
                               .WithRequired();
    
    modelBuilder.Entity<Shipment>().HasRequired(u => u.DeliveryAddress)
                                   .WithOptional();
}
Also it worth mentioning that in CTP5, when we are mapping a one-to-one association with fluent API, we don't need to specify the foreign key as we would do when mapping a one-to-many association with HasForeignKeymethod. Since EF only supports one-to-one primary key associations it will automatically create the relationship in the database based on the primary keys and we don't need to state the obvious as we did in CTP4.

Database Schema

The mapping result for our object model is as follows (note the Identity column):

Referential Integrity

In relational database design the referential integrity rule states that each non-null value of a foreign key must match the value of some primary key. But wait, how does it even applies here? All we have is just three primary keys referencing each other. Who is the primary key and who is the foreign key? The best way to find the answer of this question is to take a look at the properties of the relationships in the database that has been created by Code First:
As you can see, Code First adds a foreign key constraint which links the primary key of the Addresses table to the primary key of the Users table and adds another foreign key constraint that links the primary key of the Shipments table to the primary key of the Addresses table. The foreign key constraint means that a user has to exist for a particular address but not the other way around. In other words, the database guarantees that an Addresses row’s primary key references a valid Users primary key and a Shipments row’s primary key references a valid Addresses primary key.

How Code First Determines Principal and Dependent?

Code First has rules to determine the principal and dependent ends of an association. For one-to-many relationships the many end is always the dependent, but it gets a little tricky in one-to-one associations. In one-to-one associations Code First decides based on our object model, and possible data annotations or fluent API that we may have. For example in our case, we wrote this fluent API code to configure User-Address association:
modelBuilder.Entity<User>().HasOptional(u => u.BillingAddress).WithRequired();
This reads as "User entity has an optional association with one Address object but this association is required for Address entity."
For Code First this is good enough to make the decision: It marked User as the principal end and Address as the dependent end in the association. Since we have the same fluent API code for the second association between Address and Shipment, it marks Address as the principal end and Shipment as the dependent end in this association as well.

The referential integrity that we saw, is the first result of this Code First's principal/dependent decision.

Second Result of Code First's Principal/Dependent Decision: Database Identity

If you take a closer look at the above DB schema, you'll notice that only UserId has a regular identifier generator (aka Identity or Sequence) and AddressId and ShipmentId does not. This is a very important consequence of the principal/dependent decision for one-to-one associations: the dependent primary key will become non-Identity by default. This make sense because they share their primary key values and only one of them can be auto generated and we need to take care of providing valid keys for the rest.

What about Cascade Deletes?

As we saw, each Address always belongs to one User and each Shipment always delivered to one single Address. We want to make sure that when we delete a User the possible dependent rows on Address and Shipment also get deleted in the database. In fact, this is one of the Referential Integrity Refactorings which called Introduce Cascading Delete. The primary reason we would apply "Introduce Cascading Delete" is to preserve the referential integrity of our data by ensuring that related rows are appropriately deleted when a parent row is deleted. By default, Code First does not enable cascade delete when it creates a relationship in the database. As always we can override this convention with fluent API:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>().HasOptional(u => u.BillingAddress)
                               .WithRequired()
                               .WillCascadeOnDelete();
    
    modelBuilder.Entity<Shipment>().HasRequired(u => u.DeliveryAddress)
                                   .WithOptional()
                                   .WillCascadeOnDelete();
}

What If Both Ends are Required?

We saw that the only reason Code First could figure out principal and dependent in our 1:1 associations was because our fluent API code clearly specified one end as Required and the other as Optional. But what if both endpoints are the same in terms of being required in the association? For example what if in our domain model, User always has one Address and Address always has one User (required on both end)? The answer is that ultimately, this scenario need to be configured by fluent API and the interesting point is that fluent API is designed in a way that will force you to explicitly specify who is dependent and who is principal in such cases that this cannot be inferred by Code First.

To illustrate the idea, let's see how we can configure mapping for this User-Address association (Required/Required) with fluent API:
modelBuilder.Entity<User>().HasRequired(u => u.BillingAddress).WithRequiredDependent();
So we invoke WithRequiredDependent() after HasRequired() method. To see the reason, we need to take a look at the RequiredNavigationPropertyConfiguration type which is returned by HasRequired():
public class RequiredNavigationPropertyConfiguration
{
    public DependentNavigationPropertyConfiguration WithMany();
    public CascadableNavigationPropertyConfiguration WithOptional();
    public CascadableNavigationPropertyConfiguration WithRequiredDependent();
    public CascadableNavigationPropertyConfiguration WithRequiredPrincipal();
}
As you can see, if you want to go another Required after HasRequired() method, you have to either call WithRequiredDependent() or WithRequiredPrincipal() since there is no WithRequired() method on thisRequiredNavigationPropertyConfiguration class which is returned by HasRequired() method.
Both WithRequired and WithOptional methods return a CascadableNavigationPropertyConfiguration type which has a WillCascadeOnDelete() method. Now if we run the code and check the database, we'll see that cascade delete on both relationships are switched on.

Working with the Model

Here is an example for adding a new user along with its billing address. EF is smart enough to use the newly generated UserId for the AddressId as well:
using (var context = new EntityMappingContext())
{
    Address billingAddress = new Address()
    {   
        Street = "Yonge St.",
        City   = "Toronto"                    
    };
    User morteza = new User()
    {                    
        FirstName      = "Morteza",
        LastName       = "Manavi",
        BillingAddress = billingAddress
    }; 
            
    context.Users.Add(morteza);                        
    context.SaveChanges();                
}
The following code is an example of adding a new Address and Shipment for an existing User (assuming that we have a User with UserId=2 in the database):
using (var context = new EntityMappingContext())
{
    Address deliveryAddress = new Address()
    {
        AddressId = 2,
        Street    = "Main St.",
        City      = "Seattle"
    };
    Shipment shipment = new Shipment()
    {
        ShipmentId      = 2,
        State           = "Shipped",
        CreatedOn       = DateTime.Now,
        DeliveryAddress = deliveryAddress
    };                
               
    context.Shipments.Add(shipment);
    context.SaveChanges();      
}

Limitations of This Mapping

There are two important limitations to associations mapped as shared primary key:
  • Difficulty in saving related objects: The main difficulty with this approach is ensuring that associated instances are assigned the same primary key value when the objects are saved. For example, when adding a new Address object, it's our responsibility to provide a unique AddressId that is also valid (a User can be found with such a value as UserId.)

  • Multiple addresses for User is not possible: With this mapping we cannot have more than one Address for User. At the beginning of this post, when we introduce our model, we assumed that the user has the same address for billing and delivery. But what if that's not the case? What if we also want to add a Home address to User for the deliveries? In the current setup, each row in the User table has a corresponding row in the Address table. Two addresses would require an additional address table, and this mapping style therefore wouldn’t be adequate.

Summary

In this post we learned about one-to-one associations which shared primary key is just one way to implement it. Shared primary key associations aren’t uncommon but are relatively rare. In many schemas, a one-to-one association is represented with a foreign key field and a unique constraint. In the next posts we will revisit the same domain model and will learn about other ways to map one-to-one associations that does not have the limitations of the shared primary key association mapping. 

четверг, 27 сентября 2012 г.

Send email from your Windows Azure


Send email from your Windows Azure

As you may be know, Windows Azure doesn't provide of any email service such as a smtp server or email web service. As workaround, you can found multiple solutions on the web (like using Office 365, installing your own smtp server, ...) but you propably did not know (like me :) ) that Microsoft as a partnership with sendgrid.
SendGrid offers 25.000 email per month for each Windows Azure subscriber !!!

понедельник, 24 сентября 2012 г.

Lambda IEqualityComparer


You’ve probably used an IEqualityComparer before.  If you have, you probably know that using one is a pain because you need to implement the interface in your own class and override your Equals and GetHashCode methods.  In this post, we’ll focus on the most common scenario for checking equality: primary key comparisons.  For example, the CustomerID property of a Customer class.  This article offers a simple solution inspired by a StackOverflow post.  We’ll use lambda expressions instead of subclassing IEqualityComparer for each class, thus eliminating lots of code and making everything far more readable.
A simple example should clarify the situation.  Here’s the traditional way you’d use an IEqualityComparer to compare two Customer objects.
First extend from IEqualityComparer:
public class CustomerComparer : IEqualityComparer
{
    public bool Equals(Customer x, Customer y)
    {
        return x.CustomerID == y.CustomerID;
    }
 
    public int GetHashCode(Customer obj)
    {
        return obj.CustomerID.GetHashCode();
    }
}
Then you would use this new CustomerComparer class like so:
// First parameter == CustomerID
var x = new Customer(1);
var y = new Customer(2);
 
var list = new List()
{
    new Customer(1),
    new Customer(2)
};
 
list.Contains(new Customer(1), new CustomerComparer());
As you can see, quite a bit of code just to find an object in a collection. (In this example, if we had not used our CustomerComparer, we would not have been able to find this new instance of the same customer in our list.)  Most of the time, the comparisons we want to make are pretty simple and could be expressed using a single line of code. Allow me to introduce my class (strongly inspired from this StackOverflow post) before showing a code example. This class allows us to pass a simple lambda expression to construct an IEqualityComparer on the fly:
public class KeyEqualityComparer : IEqualityComparer
{
    private readonly Funcbool
> comparer;
    private readonly Funcobject
> keyExtractor;
 
    // Allows us to simply specify the key to compare with: y => y.CustomerID
    public KeyEqualityComparer(Funcobject
> keyExtractor) : this(keyExtractor, null) { }
    // Allows us to tell if two objects are equal: (x, y) => y.CustomerID == x.CustomerID
    public KeyEqualityComparer(Funcbool
> comparer) : this(null, comparer) { }
 
    public KeyEqualityComparer(Funcobject
> keyExtractor, Funcbool> comparer)
    {
        this.keyExtractor = keyExtractor;
        this.comparer = comparer;
    }
 
    public bool Equals(T x, T y)
    {
        if (comparer != null)
            return comparer(x, y);
        else
        {
            var valX = keyExtractor(x);
            if (valX is IEnumerable<object>) // The special case where we pass a list of keys
                return ((IEnumerable<object>)valX).SequenceEqual((IEnumerable<object>)keyExtractor(y));
 
            return valX.Equals(keyExtractor(y));
        }
    }
 
    public int GetHashCode(T obj)
    {
        if (keyExtractor == null)
            return obj.ToString().ToLower().GetHashCode();
        else
        {
            var val = keyExtractor(obj);
            if (val is IEnumerable<object>) // The special case where we pass a list of keys
                return (int)((IEnumerable<object>)val).Aggregate((x, y) => x.GetHashCode() ^ y.GetHashCode());
 
            return val.GetHashCode();
        }
    }
}
Here is how you would use this code:
var x = new Customer(1);
var y = new Customer(2);
 
var list = new List()
{
    new Customer(1),
    new Customer(2)
};
 
list.Contains(new Customer(1), z => z.CustomerID);
To be able to use this in Contains, we need to add a new extension method (which was not included in the original StackOverflow post):
public static bool Contains(this IEnumerable list, T item, Funcobject
> keyExtractor)
{
    return list.Contains(item, new KeyEqualityComparer(keyExtractor));
}
Once you’ve done this work for Contains, it is very easy to create extensions methods for Distinct, Except, Union, etc. Furthermore, the same principles also apply toIComparer.
For a complete source code with all the classes and extension methods, you can go here: http://gist.github.com/391397
You can modify this code by forking the gist; don’t be shy to add useful methods!

воскресенье, 23 сентября 2012 г.

How to get rid of blank pages in PDF exported from SSRS

Many Reporting Services users ask this question:

"I'm rendering to a physical page format (PDF, Image, Print) and for some reason a blank page gets inserted between all of the other pages in the report.  Thank you, Reporting Services, for the free pages.  It's a charming feature, but paper ain't free and I'm getting paper cuts pulling out every other blank page after it's printed.  How do I make it stop?!"

To which I reply:

This is almost always caused by the Body of your report being too wide for your page.  Make sure that the Width of the Body is less than the physical page size, less the margins.  For those of you who are into the math thing, make sure that:

Body Width <= Page Width - (Left Margin + Right Margin)

For physical page renderers, it's important to keep in mind the concept of Usable Area.  The Usable Area is the amount of space left on a page for your report to be drawn after reserving space for margins, headers and footers, and column spacing (for multi-column reports).

Horizontal usable area:

X = Page.Width - (Left Margin + Right Margin + Column Spacing)

Vertical usable area:

Y = Page.Height - (Top Margin + Bottom Margin + Header Height + Footer Height)

Any time objects on your report (including the Body itself) extend past the usable area either because they are defined like that or because they grow at runtime, the physical page renderers have no choice but to break onto a new page.

Let me know if there are more questions about this.

вторник, 18 сентября 2012 г.

How to render client report definition files (.rdlc) directly to the Response stream without preview


ReportViewer control is normally used to open a report definition file, process it and load it into the viewing area.
The simple method below allows you to render the report directly to the response stream without using the ReportViewer control. This might be useful in cases where you want to render a non interactive report.
The example below renders the report in PDF format. The other report types available when using the LocalReport.Render method are “Excel”and “Image”.


/// 
/// References:
/// 
private void RenderReport() {
    LocalReport localReport = new LocalReport();
    localReport.ReportPath = Server.MapPath("~/Report.rdlc");
  
    //A method that returns a collection for our report
    //Note: A report can have multiple data sources
    List<Employee> employeeCollection = GetData();

    //Give the collection a name (EmployeeCollection) so that we can reference it in our report designer
    ReportDataSource reportDataSource = new ReportDataSource("EmployeeCollection", employeeCollection);
    localReport.DataSources.Add(reportDataSource);

    string reportType = "PDF";
    string mimeType;
    string encoding;
    string fileNameExtension;

    //The DeviceInfo settings should be changed based on the reportType
    //http://msdn2.microsoft.com/en-us/library/ms155397.aspx
    string deviceInfo =
    "" +
    "  PDF" +
    "  8.5in" +
    "  11in" +
    "  0.5in" +
    "  1in" +
    "  1in" +
    "  0.5in" +
    "
";

    Warning[] warnings;
    string[] streams;
    byte[] renderedBytes;

    //Render the report
    renderedBytes = localReport.Render(
        reportType,
        deviceInfo,
        out mimeType,
        out encoding,
        out fileNameExtension,
        out streams,
        out warnings);

    //Clear the response stream and write the bytes to the outputstream
    //Set content-disposition to "attachment" so that user is prompted to take an action
    //on the file (open or save)
    Response.Clear();
    Response.ContentType = mimeType;
    Response.AddHeader("content-disposition""attachment; filename=foo." + fileNameExtension);
    Response.BinaryWrite(renderedBytes);
    Response.End();

}

Note that if you change the ReportType in the Render method, you will also have to change the DeviceInfo settings. 

воскресенье, 16 сентября 2012 г.

Serializing Entity Framework Objects into JSON Using ASP.Net MVC


I’ve been working with the Entity Framework 4 on a new MVC2 application recently and one apparently simple task had been doing my head in for a full day. The problem was when I tried to serialize a list entity object using the Json method in a JsonResult action. Every time I tried, no matter how I approached the task of retrieving the records, I’d always see the following exception in my Json response:
A circular reference was detected while serializing an object of type ‘System.Data.Metadata.Edm.AssociationType’.
The really frustrating part was that when I was debugging my application in Visual Studio, everything appeared to be working just fine. Connections were established, records were returned and could be passed into my List object.
As it turns out, the problem appears to be a native issue with the DataContractJsonSerializer support for Entity types.  In short, Entities that have relationships (i.e. two-way) with other Entity types cannot be serialised through Json.  For example, a Customer table connected to an Orders table will not translate well into Json serializing because a Customer may have many orders and an Order is associated with a Customer.
Eugene Osovetsky, Program Manager (Connected Framework Client) at Microsoft has the following to say about the issue:
The lack of DataContractJsonSerializer support for Entity types is certainly an important feature hole, and we will certainly be investigating possible solutions in future versions. Unfortunately, the current serialization model only allows one projection per type, EDM type generation only allows generating IsReference=true types, and there is no standard way of encoding references in JSON. Clearly one of these 3 constraints has to give for this to be solved, but all have problems. Multiple projections would be great, but may be complex to understand/use and would be a major new feature (it is on our radar for the future).
One approach to solving this problem has been:
…to make the serializer ignore the properties of type EntityReference, using an empty implementation of a class deriving from JavaScriptConverter and registering it using the RegisterConverters method of the JavaScriptSerializer object.
However, I feel that for most cases, this is overly complex and requires more thought than most situations would likely demand.  A better approach IMHO would be to take a collection of results and extract your required properties into a new anonymous type.  The thinking here is that if you’re returning Json objects through Ajax calls, you’re probably only interested in values rather than the full behaviours of a given object.  Therefore, the following code sorted my problem out adequately:
public JsonResult Orders()
    var results = from Ord in databaseContext.Orders
              select new
              {
                  OrderID = Ord.ID,
                  OrderTitle = Ord.Name,
                  OrderDate = Ord.OrderDate
              }

    return Json(results);
}
This is an easier approach because I gain control and flexibility in exactly what values are returned in my Json response, I can ignore completely the buggy nature of the DataContractJsonSerializer and I’m serializing only the object parts I’m concerned with.