CODEWITHSUNDEEP
ormlite-servicestack
ristaloff
ristaloff

Reputation: 130

How to make a query that returns a dictionary with values from two joined tables in Servicestack.Ormlite?

I've got two tables, Card and User. Card has a reference to user, so that a user can have many cards, but a card can only have one user. I have made this mysql-query that returns the Card.Id and User.LastName:

SELECT Card.Id,User.LastName FROM Card
    left join User on Card.UserId = User.Id
    where Card.Id = '1';

How can I make that query with servicestack ormlite? I've tried something like this:

var cardIdAndUserName = db.Dictionary<int, string>(db.From<Card>()
    .LeftJoin<Card, User>((c,u) => c.UserId == u.Id)
    .Select(card => new {card.id, user.Lastname}));

I could not get the code above to work because the .Select statement will only let me get columns from the Card table. I guess I could make a db.Select(InfoINeedObject) intead of the db.dictionary call, but then I have to create a new class, InfoINeedObject.

Upvotes: 1

Views: 340

Answers (1)

mythz
mythz

Reputation: 143349

There are new Select Overloads that allows you to specify multiple tables added in this commit which will now let you do:

var cardIdAndUserName = db.Dictionary<int, string>(db.From<Card>()
    .LeftJoin<Card, User>()
    .Select<Card,User>((card,user) => new {card.id, user.Lastname}));

Note: Where joins can be implied it doesn't need to be specified

This change is available from v4.0.39+ that's now available on MyGet.

Upvotes: 3

Related Questions