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