ORMLite - Left join column ambiguity

ServiceStack.OrmLite
1

Hi all,

I got the following orm lite query:

        return await connection.ColumnDistinctAsync<Guid>(
            connection.From<Contact>()
                .Where<Contact>(x =>
                    x.UserId == req.UserId)
                .LeftJoin<Items>((contact, items) =>
                    contact.UserId == items.UserId &&
                    contact.ContactId == items.ContactId)
                .And<Items>(x => x.ContactId == null), token: token);

Unfortunately this query throws because of “Ambiguous column” exceptions. I thought that by adding the anonymous parameters in the left join in enough for the aliasing?

Thanks

2

Try moving all JOINS before any Where clauses as they would be in normal SQL.

3

Thats the same - Thats how I had it all along. I actually moved the where above the join after seeing an answer on this forums.

4

Can you provide the full query + types including the select clause that selects the Guid column.

Which answer was this?

5

So the query is:

        return await connection.ColumnDistinctAsync<Guid>(
            connection.From<Contact>()
                .LeftJoin<Items>((contact, items) =>
                    contact.UserId == items.UserId &&
                    contact.ContactId == items.ContactId)
                .Where<Contact>(x =>
                    x.UserId == req.UserId &&
                    x.WebsiteId == req.WebsiteId)
                .And<Items>(x => x.ContactId == null), token: token);

The Cart and Items objects are simple pocos as I don’t like putting ServiceStack attributes on those common that are shared between projects. Instead, I have two mappings like those:

public class ContactMapping : OrmLiteMapping<Contact>
{
    public ContactMapping()
    {
        MapToTable("contacts");
        MapToSchema("something");

        MapToColumn(p => p.Name, "name");
        MapToColumn(p => p.UserId, "user_id");
        MapToColumn(p => p.ContactId, "contact_id");
    }
}

and items

public class ItemsMapping : OrmLiteMapping<Items>
{
    public ItemsMapping()
    {
        MapToTable("items");
        MapToSchema("something");

        AutoIncrement(p => p.ItemsId);
        MapToColumn(p => p.ItemsId, "id");
        MapToColumn(p => p.UserId, "user_id");
        MapToColumn(p => p.ContactId, "contact_id");
        MapToColumn(p => p.CreatedOn, "created_on");
        MapToColumn(p => p.CompletedOn, "completed_on");
    }
}
6

Please provide the class definition of the Contact and Items Types please, not sure what OrmLiteMapping<T> is, it’s not part of OrmLite.

Also you’re not specifying which field should be selected? I’m expecting to see something like:

    .Select(x => x.ContactId)

Or the field you want ColumnDistinctAsync<Guid> to return

7

The Contact class is:

public class Contact
{
    public Guid UserId { get; set; }
    public Guid ContactId { get; set; }
    public String Name { get; set; }
}

The Items class is:

public class Items
{
    public int ItemsId { get; set; }
    public Guid UserId { get;set; }
    public Guid ContactId { get;set; }
    public DateTime? CompletedOn { get;set; }
}

The OrmLiteMapping class is just a set of convenience methods to achieve the same effect as adding the necessary attributes on the POCO objects.

public abstract class OrmLiteMapping<TEntity> : IOrmLiteMapping
{
    protected void Ignore(Expression<Func<TEntity, object>> expression)
    {
        var info = (PropertyInfo)ReflectionHelper.GetMemberInfo(expression);
        info.AddAttributes(new IgnoreAttribute());
    }

    protected void AutoIncrement(Expression<Func<TEntity, object>> expression)
    {
        var info = (PropertyInfo)ReflectionHelper.GetMemberInfo(expression);
        info.AddAttributes(new AutoIncrementAttribute());
    }

    protected void MapToColumn(Expression<Func<TEntity, object>> expression, String columnName)
    {
        var info = (PropertyInfo)ReflectionHelper.GetMemberInfo(expression);
        info.AddAttributes(new AliasAttribute(columnName));
    }

    protected void MapToTable(String tableName)
    {
        typeof(TEntity).AddAttributes(new AliasAttribute(tableName));
    }

    protected void MapToSchema(String schema)
    {
        typeof(TEntity).AddAttributes(new SchemaAttribute(schema));
    }
}

The error I m getting after running the query above is:

Result Message:	
System.AggregateException : One or more errors occurred.
  ----> System.Data.SqlClient.SqlException : Ambiguous column name 'user_id'.
Ambiguous column name 'contact_id'.

The service stack version I m using is 4.5.4

You are correct about the missing select but thats not the problem in this case

8

Actually you know what? I just added the missing select at the end and it just worked! I didnt expect that ServiceStack would throw this message with the missing select! Thanks for your help!

1 Like