Using ColumnDistinct with join

ServiceStack.OrmLite
1

Maybe I’ve got the wrong syntax here, but I can’t see to get a ColumnDistinct to work with a join as it gives me an Ambiguous column name error since it doesn’t prepend the column name with the table name.

Given the following simplified models…

public class Customer
{
    public int Id { get; set; }
    public bool IsInactive { get; set; }

    [Reference]
    public List<ProfileCode> ProfileCodes { get; set; }
}

public class ProfileCode
{
    public int Id { get; set; }
    [References(typeof(Customer))]
    public int CustomerId { get; set; }
    public string CodeType { get; set; }
    public string CodeValue { get; set; }
}

I can get the list of non-mailable customers with custom SQL like…

public HashSet<int> SelectCustomerIdsForNoMail()
{
    using (var db = _DBFactory.OpenDbConnection())
    {
        return db.ColumnDistinct<int>(@"
            select distinct c.Id
            from Customer c
            join ProfileCode pc on c.Id = pc.CustomerId
            where
                c.IsInactive = 0 and
                pc.CodeType = 'MailCode' and pc.CodeValue in ('No Mail', 'Bad Address')");
    }
}

…but I’d rather not use custom SQL and would like to do something like…

public HashSet<int> SelectCustomerIdsForNoMail()
{
    using (var db = _DBFactory.OpenDbConnection())
    {
        var query = db.From<Customer>()
            .Select<int>((Customer c) => c.Id)
            .Join<Customer, ProfileCode>((c, pc) => c.Id == pc.CustomerId)
            .Where<Customer>(c => c.IsInactive == false)
            .And<ProfileCode>(pc => pc.CodeType == "MailCode" && Sql.In(pc.CodeValue, new[] { "No Mail", "Bad Address" }));

        return db.ColumnDistinct<int>(query);
    }
}

…however that is giving me Ambiugous column name 'Id' because it generates the following SQL…

SELECT "Id"
FROM "Customer" INNER JOIN "ProfileCode" ON ("Customer"."Id" = "ProfileCode"."CustomerId")
WHERE ("Customer"."IsInactive" = 0) AND (("ProfileCode"."CodeType" = 'MailCode') AND "ProfileCode"."CodeValue" In ('No Mail','Bad Address'))

Can I not do this, or do I have my syntax wrong for the pull?

2

Always have the Select() statement at the end like it is with LINQ, and you shouldn’t need to specify .Select<int>, try:

var badCodes = new[] { "No Mail", "Bad Address" };

var q = db.From<Customer>()
    .Join<Customer, ProfileCode>()
    .Where<Customer>(c => c.IsInactive == false)
    .And<ProfileCode>(pc => pc.CodeType == "MailCode" && badCodes.Contains(pc.CodeValue))
    .SelectDistinct(c => c.Id);

var ids = db.ColumnDistinct<int>(q);
ids.PrintDump();
1 Like
3

Perfect! That works just dandy. Also didn’t realize we could now do a .Contains to translate to a Sql.In…nice!

Thanks Demis!

4

oooh, thanks for pointing out the Sql.In / Contains part !