Simple.Data a projection into a dto

Sorry for the long delay since my last post but my wife gave birth to our son Nico a few weeks ago and to honest this has taken up all my time....

Anyway back to the blog, today I decided to have a quick look into Simple.Data. First I created a new console application and then installed the package via nuget:-

PM> Install-Package Simple.Data.Mysql

If you are using Sql Server then just use:-

PM> Install-Package Simple.Data.SqlServer

The first thing I decided to try was projecting a couple of columns from a single table into a DTO. My schema looks like this (note mysql):-

var db = Database.OpenConnection("server=1.1.1.1;user=usera;database=wildesoft;password=*********;");

IEnumerable<CmsPageDto> pages = db.CmsPage
  .All()
  .Select(db.CmsPage.PageId.As("Id"), db.CmsPage.PageName)
  .Cast<CmsPageDto>();

foreach (var page in pages) {
  Console.WriteLine(page.Id + " " + page.PageName);
}

The dto looks like this:-

public class CmsPageDto {
  public int Id { get; set; }
  public string PageName { get; set; }
}

One thing that I could not fathom out was how to change the column PageId to just Id. After downloading the Simple.Data source code and looked at the tests I worked out that I needed to use the .As("NewColumnName")

and volia...

The generated SQL looks like this:-

select
  cmspage.PageId AS Id,
  cmspage.PageName 
from
  cmspage

A sweet spot is that preserves the case of the table and columns names, very important for MySql server.

Boy is Simple.Data well just simple to use! I am going to use Simple.Data in my next project and hopefully be able to blog about it a bit more..