Skip to content

Instantly share code, notes, and snippets.

@mythz

mythz/main.cs

Last active Dec 2, 2020
Embed
What would you like to do?
Custom SQL Issue
using System;
using System.Linq;
using ServiceStack;
using ServiceStack.Text;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Sqlite;
using ServiceStack.DataAnnotations;
var dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
var db = dbFactory.Open(); // Open ADO.NET DB Connection
db.CreateTable<Network>();
db.CreateTable<NetworkMain>();
OrmLiteUtils.PrintSql();
public class Network
{
[PrimaryKey]
public string Id { get; set; }
public string Name { get; set; }
[Ignore]
public bool InMaintenance { get; set; }
}
public class NetworkMain
{
[PrimaryKey]
public string Id { get; set; }
[ForeignKey(typeof(Network))]
public string NetworkId { get; set; }
public DateTime? EndDate { get; set; }
}
public class NetworkDTO
{
public string Id { get; set; }
public string Name { get; set; }
public bool InMaintenance { get; set; }
}
var q = db.From<Network>()
.LeftJoin<NetworkMain>()
.Select<Network, NetworkMain>(
(a, m) => new
{ a,
InMaintenance = m.NetworkId == a.Id && m.EndDate.HasValue ? "1" : "0"
}).OrderBy(x=>x.Name);
var results = db.Select<NetworkDTO>(q).ToList();
<?xml version="1.0" encoding="utf-8"?>
<packages>
<package id="System.Memory" version="4.5.4" targetFramework="net45" />
<package id="ServiceStack.Text" version="5.10.0" targetFramework="net45" />
<package id="ServiceStack.Client" version="5.10.0" targetFramework="net45" />
<package id="ServiceStack.Common" version="5.10.0" targetFramework="net45" />
<package id="ServiceStack.Interfaces" version="5.10.0" targetFramework="net45" />
<package id="ServiceStack.OrmLite" version="5.10.0" targetFramework="net45" />
<package id="ServiceStack.OrmLite.Sqlite.Windows" version="5.10.0" targetFramework="net45" />
</packages>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment