Join with Alaised columns

ServiceStack.OrmLite SQL Server
1

Hi,

I’m trying to join using Db.Select<TRes>(q) where q is a Db.From<T>.LeftJoin<T2> and doesn’t use the q.Select API. TRes has all props from T but only the one I want from T2. I can see there is a convention coming into effect and if I call my prop T2NamePropName (e.g. ArtistName) then it will automatically be generated in the SQL select statement, but if I don’t follow that convention, and use an alias, the alias doesn’t get respected.

I’ve reproduced in a gistlyn. http://gistlyn.com/?gist=49b46c5dca699bb1e69f6aed294a832a&collection=2cc6b5db6afd3ccb0d0149e55fdb3a6a

Is this intended behaviour, or is there any way around it?

Thanks,
Chris

2

The purpose of the Alias is to map back to the original RDBMS column name if it’s different. The Alias also only has an affect when it’s on the Source table of the query e.g. q.From<TSource1>().Join<TSource2>() where it’s used to modify the query that’s generated where it selects the Alias column name instead of the property name.

When selecting into a different table, e.g:

var results = db.Select<TrackArtist>(q);

The “Select Into” table TrackArtist has no effect on the query that’s generated, it’s only used to populate the results from the returned resultset.

The field mapping convention is documented in Select data from multiple tables into a Custom POCO.

3

That isn’t what I was seeing under SQL Profiler. Unless we’re talking about something different? I’ve reproduced in the gist http://gistlyn.com/?gist=49b46c5dca699bb1e69f6aed294a832a&collection=2cc6b5db6afd3ccb0d0149e55fdb3a6a
And you can see that the captured sql statements are different.

I don’t suppose there are any ways to override the rules? With an [Alias] type attribute, or ideally, using the [Alias] attribute?

4

Sorry I meant if you have a Custom Select in the SqlExpression the SelectInto<T> table doesn’t have any effect, which is typically why you’d select into a Custom Table to match the custom schema of the returned resultset.

Not entirely sure what you’re trying to do but if you want to populate ArtistIsCalled with the artist name you can use a custom select:

var q = db.From<Track>()
    .Join<Artist>((x, y) => x.ArtistId == y.Id)
    .OrderByDescending(x => x.Year)
    .Take(3)
    .Select<Track,Artist>((t,a) => new {
        t.Name,
        ArtistIsCalled = a.Name,
        t.Album
    });

public class TrackArtist 
{
    public string Name { get; set; }
    public string ArtistIsCalled { get; set; }
    public string Album { get; set; }
}

var results = db.Select<TrackArtist>(q);
5

Ah ok.

For the custom select, it’s just the extra lines are a nuisance. (It previously worked on my hacked join in v3, which is why it’s an issue for me now.) So I’ve about 170 invocations to check and potentially fix.