Hi,

LLBLGenPro v3.5 Build Date: June 27th, 2012 (latest)

When two tables inner join in QuerySpec, it produces an SQL with "DISTINCT" query as follow:

var factory = new QueryFactory();
var query = factory.SaleEvent.From(QueryTarget.InnerJoin(SaleEventEntity.Relations.PersonEntityUsingPrimaryId));
var result = this.AdapterToUse.FetchQuery(query);

SQL Output: SELECT DISTINCT ...

However, The following QuerySpec does not append "DISTINCT" in SQL output:

var factory = new QueryFactory();
var query = factory.SaleEvent.From(QueryTarget.InnerJoin(SaleEventEntity.Relations.PersonEntityUsingPrimaryId)).Select(() => new {
                    RecordId = SaleEventFields.PrimaryId.ToValue<int>(),
                    CompanyName = PersonFields.CompanyName.ToValue<string>(),
                    });
var result = this.AdapterToUse.FetchQuery(query);

SQL Output: SELECT ...

In the above query, I tried many columns in Select() Func, but there is no "DISTINCT" in SQL output. Also, Linq do not produce "DISTINCT" as well:

var metaData = new LinqMetaData(this.AdapterToUse);
var q = (from e in metaData.SaleEvent
            join c in metaData.Person on e.PersonId equals c.PrimaryId
            select new { e, c });

SQL Output: SELECT ...

I am not sure why the first QuerySpec creates "DISTINCT" keyword in SQL query. Any ideas?

Kind Regards,

The first query is selecting entities, and with a join a Distinct becomes a must as to not return duplicates.

The second query is projecting some fields into a new type, and thus no entities to return.

Hi,

Thanks for your feedback. The first query only returns "SaleEvent" instance, and does not return "Person" entity.

I added a full projection in Select - but it does not return both entities together.

var factory = new QueryFactory();
var query = factory.SaleEvent.From(QueryTarget.InnerJoin(SaleEventEntity.Relations.PersonEntityUsingPrimaryId)).Select(Projection.Full);
var result = this.AdapterToUse.FetchQuery(query);

I do not want to manually add all fields in "Select" projection as it would be lots of fields in every join query. For example, the following Linq query returns both entities with anonymous type:

var metaData = new LinqMetaData(this.AdapterToUse);
var q = (from saleEvent in metaData.SaleEvent
            join person in metaData.Person on saleEvent.PersonId equals person.PrimaryId
            select new { saleEvent, person });

// For example: 
q.First().saleEvent.PrimaryId.Dump();
q.First().person.PrimaryId.Dump();

Would it be possible to fetch a join query with both entities - similar to the Linq query?

Regards.

The Projection.Full only works for EntityQuery<>. In DynamicQueries you have to specify the list of fields, it's the only way AFAIK.