Left join with Entity Framework (Method Syntax)

Add Comment | Feb 15, 2013

Here is a problem I encountered recently trying to perform a simple SQL operation using method syntax of Entity Framework.

I had 2 IEnumerable<Object> datasets (dataset1 and dataset2). I wanted to join them to obtain a third dataset without loosing dataset1 records not referencing dataset2 records. To put it simply, I needed a Left Join from dataset1 to dataset2.

The syntax would be straightforward in SQL; it would go like that :

SELECT D1.Id, D1.Whatever, D2.Id, D2.Something
FROM dataset1 D1
LEFT OUTER JOIN dataset2 D2

 

However, it is not so easy with Entity Framework. Here is a simple example of how to do it :

using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApplication1
{
    //  An example of SQL's LEFT OUTER JOIN equivalent using Entity Framework :
    //  Inputs : A dataset of Employees and a dataset of Offices. Each Employee record is holding an optional reference to its assigned Office.
    //  Goal : List the employees along with their (optional) assigned Office
    //
    //  Ex :
    //  Id FirstName Name       Office
    //  ---------------------------------
    //  1  John      Smith      New York
    //  2  Jean      Durand     Paris
    //  3  Oleg      Kouletchov
    //  4  Bobby     Lost
    //
    //

    // Item classes : Office, Employee and ResultClass
    public class Office
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
    public class Employee
    {
        public int Id { get; set; }
        public int? IdOffice { get; set; } // <- an office can be null
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }
    public class ResultClass  // result records as we want them
    {
        public int IdEmployee { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string WorkPlace { get; set; }
    }
    

    class Program
    {
        static void Main(string[] args)
        {
            // populate datasets
            var listQueryableOffice = new List<Office>()
                                 {
                                     new Office() {Id = 1, Name = "New York"},
                                     new Office() {Id = 2, Name = "Paris"}
                                 }.AsQueryable();
            var listQueryableEmployees = new List<Employee>()
                                    {
                                        new Employee()
                                            {
                                                Id = 1,
                                                FirstName = "John",
                                                LastName = "Smith",
                                                IdOffice = 1
                                            },
                                            new Employee()
                                            {
                                                Id = 2,
                                                FirstName = "Jean",
                                                LastName = "Durand",
                                                IdOffice = 2
                                            },
                                             new Employee() // This guy has no workplace assigned yet
                                            {
                                                Id = 3,
                                                FirstName = "Oleg",
                                                LastName = "Kouletchov"
                                            },
                                             new Employee() // This guy has a reference to an Office that does not exist
                                            {
                                                Id = 4,
                                                FirstName = "Bobby",
                                                LastName = "Lost",
                                                IdOffice = 7
                                            },
                                    }.AsQueryable();
            //  Perform a GroupJoin followed by a SelectMany to obtain the following SQL Query equivalent with Entity Framework :
            //  SELECT E.Id, E.FirstName, E.LASTNAME, O.NAME as WORKPLACE
            //  FROM EMPLOYEES E
            //  LEFT OUTER JOIN OFFICE O
            IEnumerable<ResultClass> joinedResults =
                listQueryableEmployees.GroupJoin(listQueryableOffice,
                                                employee => employee.IdOffice, // perform the join on IdOffice
                                                office => office.Id,
                                                (employee, offices) =>
                                                new  // Intermediate anonymous type
                                                {
                                                    IdEmployee = employee.Id,
                                                    FirstName = employee.FirstName,
                                                    LastName = employee.LastName,
                                                    WorkPlaces = offices.DefaultIfEmpty() // IEnumerable<Office> , can be null
                                                })
                                    .SelectMany(
                                        x =>
                                        x.WorkPlaces.Select(   // each (possibly empty) WorkPlace record will yield a result record
                                            place => new ResultClass()  // the result type we want
                                            {
                                                IdEmployee = x.IdEmployee,
                                                FirstName = x.FirstName,
                                                LastName = x.LastName,
                                                WorkPlace = place == null ? "" : place.Name  // workplace may be null (LEFT JOIN here!) but we still return a record
                                            }));


            // Display the results
            foreach (var joinedResult in joinedResults)
            {
                Console.WriteLine(joinedResult.IdEmployee +
                    " " + joinedResult.FirstName +
                    " " + joinedResult.LastName + " " +
                    joinedResult.WorkPlace);
            }
            Console.ReadLine();
        }
    }
}

VSDB to SSDT part 4 : Redistributable database deployment package with SqlPackage.exe

Add Comment | Sep 12, 2012

 

The goal here is to use SSDT SqlPackage to deploy the output of a Visual Studio 2012 Database project… a bit in the same fashion that was detailed here : http://geekswithblogs.net/80n/archive/2012/09/12/vsdb-to-ssdt-part-3--command-line-deployment-with-sqlpackage.exe.aspx

 

The difference is we want to do it on an environment where Visual Studio 2012 and SSDT are not installed. This might be the case of your Production server.

 

Package structure

So, to get started you need to create a folder named “DeploymentSSDTRedistributable”. This folder will have the following structure :

 

image

   

 

  • The dacpac and dll files are the outputs of your Visual Studio 2012 Database project. If your database project references another database project, you need to put their dacpac and dll here too, otherwise deployment will not work.
  • The publish.xml file is the publish configuration suitable for your target environment. It holds connexion strings, SQLVARS parameters and deployment options. Review it carefully.
  • The SqlDacRuntime folder (an arbitrary chosen name) will hold the SqlPackage executable and supporting libraries

 

Contents of the SqlDacRuntime folder

Here is what you need to put in the SqlDacRuntime folder  :

image

  

 

You will be able to find these files in the following locations, on a machine with Visual Studio 2012 Ultimate installed :

  • C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin :

SqlPackage.exe

Microsoft.Data.Tools.Schema.Sql.dll 

Microsoft.Data.Tools.Utilities.dll

Microsoft.SqlServer.Dac.dll

  • C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.TransactSql.ScriptDom\v4.0_11.0.0.0__89845dcd8080cc91

Microsoft.SqlServer.TransactSql.ScriptDom.dll

 

Deploying

 

Now take your DeploymentSSDTRedistributable deployment package to your remote machine. In a standard command window, place yourself inside the DeploymentSSDTRedistributable  folder.

 

You can first perform a check of what will be updated in the target database. The DeployReport task of SqlPackage.exe will help you do that. The following command will output an xml of the changes:

 

"SqlDacRuntime/SqlPackage.exe" /Action:DeployReport /SourceFile:./Our.Database.dacpac /Profile:./Release.publish.xml /OutputPath:./ChangesToDeploy.xml

 

image

   You might get some warnings on Log and Data file like I did. You can ignore them. Also, the tool is warning about data loss when removing a column from a table. By default, the publish.xml options will prevent you from deploying when data loss is occuring (see the BlockOnPossibleDataLoss inside the publish.xml file). Before actual deployment, take time to carefully review the changes to be applied in the ChangesToDeploy.xml file. 

 

When you are satisfied, you can deploy your changes with the following command :

"SqlDacRuntime/SqlPackage.exe" /Action:Publish /SourceFile:./Our.Database.dacpac /Profile:./Release.publish.xml

 

Et voilà !  Your dacpac file has been deployed to your database. I’ve been testing this on a SQL 2008 Server (not R2) but it should work on 2005, 2008 R2 and 2012 as well.

 

Many thanks to Anuj Chaudhary for his article on the subject : http://www.anujchaudhary.com/2012/08/sqlpackageexe-automating-ssdt-deployment.html

VSDB to SSDT part 3 : command-line deployment with SqlPackage.exe, replacement for Vsdbcmd.exe

Add Comment | Sep 12, 2012

For our continuous integration needs, we use a powershell script to handle deployment.

A simpler approach would be to have a deployment task embedded within the build process. See the solution provided here by Jakob Ehn (a most interesting read which also dives into the '”deploying from Visual Studio” specifics) : http://geekswithblogs.net/jakob/archive/2012/04/25/deploying-ssdt-projects-with-tfs-build.aspx

 

For our needs, though, clearly separating our build phase from our deployment phase is important. It allows us to instantly deploy old versions. Also it is more convenient for continuous integration. So we stick with the powershell script approach. With VSDB projects, that script used to call the following command (the vsdbcmd executable was locally available, along with needed libraries):

vsdbcmd.exe /a:Deploy /dd /cs:<CONNECTIONSTRING TO TARGET DB> /dsp:SQL /manifest:< PATH TO .deploymanifest FILE>

 

To be able to do the approximately same thing with a SSDT produced file (dacpac), you would call this command on a machine which has VS2012 installed (or the SSDT installed, see here : http://msdn.microsoft.com/en-us/library/hh500335%28v=vs.103%29):

 

C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe /Action:Publish /SourceFile:<PATH TO Database.dacpac FILE> /Profile:<PATH TO .publish.xml FILE>

 

And from within a powershell script :

 

& "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:<PATH TO Database.dacpac FILE> /Profile:<PATH TO .publish.xml FILE>

 

The command will consume a publish.xml file where the connection string and the deployment options are specified. You must be familiar with it if you have done some deployments from visual studio. If not, please refer to the above mentioned article by Jakob Ehn.

 

It is also possible to pass those parameters in the command line. The complete SqlPackage.exe syntax is detailed here : http://msdn.microsoft.com/en-us/library/hh550080%28v=vs.103%29.aspx

VSDB to SSDT Part 2 : SQL Server 2008 Server Project … with SSDT

Add Comment | Sep 12, 2012

With Visual Studio 2012 and the use of SSDT technology, there is only one type of database project : SQL Server Database Project.

With Visual Studio 2010, we used to have SQL Server 2008 Server Project which we used to define server-level objects, mostly logins and linked servers. A convenient wizard allowed for creation of this type of projects. It does not exists anymore. Here is how to create an equivalent of the SQL Server 2008 Server Project  with Visual Studio 2012:

  • Create a new SQL Server Database Project : it will be created empty
  • Create a new SQL Schema Compare ( SQL menu item > Schema Compare > New Schema Comparison )
  • As a source, select any database on the SQL server you want to mimic
  • Set the target to be your newly Database Project
  • In the Schema Compare options (cog-like icon), Object Types pane, set the options as below. You might want to tweak those and select only the object types you want. Then, run the comparison, review and select your changes and apply them to the project.

 

image

VSDB to SSDT Part 1 : Converting projects and trimming excess files

Add Comment | Sep 12, 2012

Visual Studio 2012 introduces a change regarding Database Projects : they now use the SSDT technology, which means old VS2010 database projects (VSDB projects) need to be converted.

Hopefully, VS2012 does that for you and it is quite painless, but in my case some unnecessary artifacts from the old project were left in place.  Also, when reopening the solution, database projects appeared unconverted even if I had converted them in the previous session and saved the solution.

 

Converting the project(s)

When opening your Visual Studio 2010 solution with Visual Studio 2012, every standard project should be converted by default, but Visual Studio will ask you about your database projects :

“Functional changes required
Visual Studio will automatically make functional changes to the following projects in order to open them. The project behavior will change as a result. You will be able to open these projects in this version and Visual Studio 2010 SP1.”

If you accept, your project is converted. And it should compile with no errors right away except if you have dependencies to dbschema files which are no longer supported.

 

The output of a SSDT project is a dacpac file which replaces the dbschema file you were accustomed to. References to dacpac files can be added to SSDT projects in the same fashion references to dbschema could be added to VSDB projects.

 

Cleaning up

You will notice that your project file is now a sqlproj file but the old dbproj is still here. In fact at that point you can still reopen the solution in Visual Studio 2010 and everything should show up.

 

If like me you plan on using VS2012 exclusively, you can get rid of the following files which are still on your disk and in your source control :

  • the dbproj and dbproj.vspscc files
  • Properties/Database.sqlcmdvars
  • Properties/Database.sqldeployment
  • Properties/Database.sqlpermissions
  • Properties/Database.sqlsettings

 

You might wonder where the information which used to be in the Properties files is now stored.

  • Permissions : a Permissions.sql was created at the root level of your project. Note that when you create a new database project and import a database using the Schema Compare capabilities from Visual Studio, imported table and stored procedure definition files will hold the permission information (along with constraints and, indexes)
  • SQLVars : they are defined inside the publish.xml files
  • Deployment : they are also in the publish.xml files
  • Settings : I was unable to find where those are now. I suppose they are not defined anymore

 

But Visual Studio still says my database projects should be converted !

I had this error upon closing and then re-opening the solution : my database projects would appear unconverted even though I did all the necessary steps previously.

 

Easy solution : remove those projects from the solution and add them again (the sqlproj files).

 

More

For those who run into problems when converting from VSDB to SSDT, I suggest reading the following post : http://blogs.msdn.com/b/ssdt/archive/2011/11/21/top-vsdb-gt-ssdt-project-conversion-issues.aspx

 

Also interesting, is a side by side comparison of VSDB and SSDT project features :

http://blogs.msdn.com/b/ssdt/archive/2011/11/21/sql-server-data-tools-ctp4-vs-vs2010-database-projects.aspx

VSDB to SSDT Series : Introduction

Add Comment | Sep 11, 2012

At the office, we extensively use VS2010 SQL Server 2008 Database Projects and SQL Server 2008 Server Projects  in our Visual Studio 2010 solutions. With Visual Studio 2012, those types of projects are replaced by the  SQL Server Database Project  using the SSDT (SQL Server Data Tools) technology.

I started investigating the shift from Visual Studio 2010 to Visual Studio 2012 and specifically what needs to be done concerning those database projects in terms of painless migration, continuous integration and standalone deployment.

I will write my findings in a series of 4 short articles:

  • Part 1 will be about the database projects migration process and the cleaning up that ensues
  • Part 2 will be about creating SQL Server 2008 Server Projects equivalents with the new SSDT project type
  • Part 3 will introduce a replacement to the vsdbcmd.exe command used for deployment in our continuous integration process
  • Part 4 will explain how to create standalone packages of SSDT projects for deployment on non accessible servers (such as a production server)

Update the model on HttpPost and render the changes in the View

Add Comment | Jul 11, 2012

 

With MVC3, I came over that problem where I was rendering a view with an updated model at the end of an HttpPost and the changes to the model were never applied to the rendered view :

 

NOT working as expected !
[HttpPost]
        public ActionResult Edit(JobModel editedJobModel)
        {
            // Update some model property
            editedJobModel.IsActive = true;
            
            // The view will NOT be updated as expected
            return View(editedJobModel);
        }

 

This is the standard behavior. In MVC3, POSTing the model does not render the Html helpers again. In my example, a HiddenFor bound to the IsActive value will not have its value set to true after the view is rendered.

 

Are you stuck, then ?

 

Well, for one, you’re not supposed to do that: in an ideal world you are supposed to apply the Post/Redirect/Get pattern. You would redirect to a new GET after your POST performed its actions. That’s what I usually do, but sometimes, when maintaining code and implementing slight changes to a pre-existing and tested logic, one prefers to keep structural changes to a minimum.

 

If you really have to (but my advice is to try to implement the PRG pattern whenever possible), here is a solution to alter values of the model on a POST and have the MVC engine render it correctly :

 

Solution
[HttpPost]
public ActionResult Edit(JobModel editedJobModel)
{
    // NOT WORKING : Update some model property
    //editedJobModel.IsActive = true;

    //Force ModelState value for IsActive property
    ModelState["IsActive"].Value = new ValueProviderResult(true, "True", null);
    
    // The view will be updated as expected
    return View(editedJobModel);
}

 

As you can see, it is a “dirty” solution, as the name (as a  string) of the updated property is used as a key of the ModelState dictionary.

Also, the use of ValueProviderResult is not that straightforward.

 

But hey, it works.

Rhino Mocks, AssertWasCalled with Arg Constraint on array parameter

Add Comment | Jun 03, 2012

 

Today, I had a hard time unit testing a function to make sure a Method with some array parameters was called.

Method to be called :

void AddUsersToRoles(string[] usernames, string[] roleNames);

 

I had previously used Arg<T>.Matches on complex types in other unit tests, but for some reason I was unable to find out how to apply the same logic with an array of strings.

 

It is actually quite simple to do, T really is a string[], so we use Arg<string[]>. As for the Matching part, a ToList() allows us to leverage the lambda expression.

 

sut.PermissionServices.AssertWasCalled(
                l => l.AddUsersToRoles(
                    Arg<string[]>.Matches(a => a.ToList().First() == UserId.ToString())
                    ,Arg<string[]>.Matches(a => a.ToList().First() == expectedRole1 && a.ToList()[1] == expectedRole2)
                    )
                    );

 

Of course, iw we expect an array with 2 or more values, the math would be something like : a => a.ToList()[0] == value1 && a.ToList()[1] == value2    … etc.

filterContext.RouteData.Values null ?

Add Comment | May 03, 2012

 

I’m using ASP.NET MVC 3.

I was used to rely on filterContext.RouteData.Values to get parameters from the url

 

var idEdited = filterContext.RouteData.Values["id"];

 

This works fine as long as the url does not have specific parameters, like here:

http://www.mysite.com/myapplication/User/Edit/1234

 

However, when the parameter is not a routevalue anymore, like in the example below, then you would get a OfficeId= null when using the previous method :

http://www.mysite.com/myapplication/User/Edit/1234?OfficeId=567

When you are in that case, there is a way to get both the route value “id” and the parameter “OfficeId” using the ValueProvider  :

 


var idEdited = filterContext.Controller.ValueProvider.GetValue("id").AttemptedValue;

var idEditedOffice = filterContext.Controller.ValueProvider.GetValue("OfficeId").AttemptedValue;

 

The ValueProvider will search into route values and url parameters (also in POST parameters, apparently).

 

thanks to  http://stackoverflow.com/questions/6998809/question-about-routedata-and-valueprovider-on-asp-net-mvc3

Apply a DateTime Format in an Asp.Net MVC TextBoxFor

16 Comments | Apr 27, 2012

I use MVC4 with razor.

I ran into the problem of formating correctly a DateTime property of my model that I render in the view using a TextBoxFor MVC helper. The default formatting displays the full date with hours, minutes and seconds :

 

@Html.TextBoxFor(m => m.BeginDate)

 

displays “27/04/2012 00:00:00” in the textbox. I only needed the first part so I wanted to use the ‘d’ format.

I then tried to use DataAnnotations to impose a format to the property to display:

 

[DisplayFormat(DataFormatString = "{0:d}")]
        public DateTime BeginDate { get; set; }

 

Still, the generated textbox has the full date displayed : “27/04/2012 00:00:00”

 

There are actually 2 solutions to that :

 

 

1- Use DataAnnotations + EditorFor

 

The dataannotation DisplayFormat is actually NOT taken into account when the data is rendered with @Html.TextBoxFor helper. It applies only to the EditorFor helper.

So, keeping your dataannotation and replacing your helper by this shoud work :

 

@Html.EditorFor(m => m.BeginDate)

 

 

2 -Use TextBoxFor and alter the @Value parameter

 

If you really want your TextBoxFor, there is also a solution which I find less desirable since it tinkles with the default behavior of TextBoxFor.

You can remove the DisplayFormat data annotation and use this helper :

 

@Html.TextBoxFor(m => m.BeginDate, new { @Value = Model.BeginDate.ToString("d") })