2

How to Select data using Service.OrmLite from two tables JOINwithout creating another Poco (Coal+Data) only for that purpose.

I have Poco for Coal and for CoalData like:

class Coal {
    // PK
    public long Id {get; set;}
    public string Name {get; set;}
}

class CoalData {
    // FK
    public long Id {get; set;}
    public int Prop1 {get; set;}
    ....
}

In Dapper it's simple like:

var personWithAddress = connection
    .Query<Person, Address, Extra, Tuple<Person, Address, Extra>>
    (sql, (p, a, e) => Tuple.Create(p, a, e), splitOn: "AddressId,Id").First();
Tomasito
  • 1,864
  • 1
  • 20
  • 43

1 Answers1

1

ServiceStack.OrmLite does not support joining two or more tables like you suggest. It currently only supports simple expressions, and for Joins it recommends falling back to raw SQL queries. See here for OrmLite functionality.

For anything more complex (e.g. queries with table joins) you can still easily fall back to raw SQL queries as seen below.

However ServiceStack.OrmLite will likely get better support for joins in the near future, as this is the top open feature request, but under the commercial ServiceStack offering.

Scott
  • 21,211
  • 8
  • 65
  • 72
  • Using 3.9.71 and MS SQL Server `var coal = Service.Db.Select(@"SELECT * FROM [dbo].[Coal] AS c INNER JOIN [dbo].[CoalData] AS cd ON [c].[Id] = [cd].[CoalId] WHERE c.Id = @Id", new {Id = request.Id});` didn't replaced @Id in query – Tomasito Feb 26 '14 at 13:08
  • @Tomasito If you are using 3.9.71 you will need to use Service.Db.Query not Service.Db.Select. The documentation for v3 is here. It uses a slightly different syntax. – Scott Feb 26 '14 at 13:48
  • I overlooked this fragment. Now my tests pass. – Tomasito Feb 26 '14 at 13:52