Simple.Data explicit joins

I am delving a bit more into Simple.Data and starting to look at joins. I am going to begin with a classic one-to-many relationship between the tables cmsmeta and cmspage:-

So how does simple.data handle joins? In this blog I am going to show you explicit joins.

The following code shows how we can project 2 columns from both tables into a DTO using the on(...) form.

var db = Database.OpenConnection(...);

var pages = db.CmsPage
  .Query()
  .Join(db.CmsMeta).On(db.CmsPage.MetaId == db.CmsMeta.Id)
  .Select(
    db.CmsPage.Id, 
    db.CmsPage.PageName, 
    db.CmsMeta.Keywords.As("MetaKeywords"),
    db.CmsMeta.Title.As("MetaTitle")
  ).Cast<PageDto>();

foreach (var page in pages)
  Console.WriteLine(string.Concat(page.Id, " ", page.PageName, ",", page.MetaKeywords, "-", page.MetaTitle));

The DTO looks like this:-

internal class PageDto { {
  public long Id { get; set; }
  public string PageName { get; set; }
  public string MetaTitle { get; set; }
  public string MetaKeywords { get; set; }
}

As you can see we are joining CmsPage with CmsMeta using on(...).

There is also another way to describe an explicit join and that is to use named parameters, that is describe the join as Id: db.CmsPageMetaId, whichever you choose is down to personal taste. For example:-

var pages = db.CmsPage
  .Query()
  .Join(db.CmsMeta, Id: db.CmsPage.MetaId)
  .Select(...)

Both pieces of code produces the following:-

and in case you are wondering the generated SQL looks like this:-

select 
  cmspage.Id,
  cmspage.PageName,
  cmsmeta.Keywords AS MetaKeywords,
  cmsmeta.Title AS MetaTitle
from 
  cmspage
JOIN cmsmeta ON (cmsmeta.Id = cmspage.MetaId)

There is another type of join that is called Natural Joins which requires referential integrity and uses the Foreign Key constraints on the tables. Read more about natural joins. Unless you are using the innoDb engine in MySql then natural joins are not supported.