Hi,

I'm attempting to run this query:

(apQuery is of type IQueryable<AlliancePartnerEntity> - AlliancePartnerEntity is an llbl entity)

RepeaterPager.DataSource = (from c in apQuery
                 orderby c.CreatedOn descending
                 select new
                 {
                     c.AlliancePartnerId,
                     c.Name,
                     Contact = c.UserCollectionViaAlliancePartnerUser[0].FirstName + " " + c.UserCollectionViaAlliancePartnerUser.First().Surname,
                     c.AlliancePartnerUserUsingAlliancePartnerId[0].Phone,
                     c.UserCollectionViaAlliancePartnerUser[0].Email,
                     c.AlliancePartnerUserUsingAlliancePartnerId[0].Mobile,
                     c.CreatedOn
                 }).Distinct().Take(50).ToList();

So it doesn't like me doing [0]. I get The parameter at position 0 is of an unsupported type: MemberAccess. Also tried using .First() but that got me: Argument type 'Shivam.IR.DAL.EntityClasses.AlliancePartnerUserEntity' does not match the corresponding member type 'System.String'

So my question is, is what I am trying to do possible? This approach works great with M:1 relationships...

Oh and i'm using LLBL 2.6 latest runtimes with SQL 2005.

Also some of those lines are referencing via a M:M relationship.

Contact = c.UserCollectionViaAlliancePartnerUser[0].FirstName + " " + c.UserCollectionViaAlliancePartnerUser.First().Surname,

I think you should use a SubQuery for this one.

The indexer is not supported, as that will only work in memory of course. The First() call should work though. Very odd. I'll see if I can reproduce it or find a workaround (as it's a scalar so 'let' should work too)

When I do:

var q = from c in metaData.Customer
        orderby c.Country ascending
        select new
        {
            c.CustomerId,
            c.CompanyName,
            c.Country,
            Name = c.EmployeeCollectionViaOrder.First().FirstName + c.EmployeeCollectionViaOrder.First().LastName
        };

I get an error that the function call of '+' can't contain queries. This is because the c.EmployeeCollectionViaOrder.First() call is a separate query.

When I do:

var q = from c in metaData.Customer
        orderby c.Country ascending
        select new
        {
            c.CustomerId,
            c.CompanyName,
            c.Country,
            FirstName = c.EmployeeCollectionViaOrder.First().FirstName,
            LastName = c.EmployeeCollectionViaOrder.First().LastName
        };

it works, though I have to concat the names myself in memory.

When I move the firstname and lastname fetches to let statements AFTER the orderby, I get a problem in the SQL with an unaliased usage of a table:

var q = from c in metaData.Customer
        orderby c.Country ascending
        let firstName = c.EmployeeCollectionViaOrder.First().FirstName
        let lastName = c.EmployeeCollectionViaOrder.First().LastName
        select new
        {
            c.CustomerId,
            c.CompanyName,
            c.Country,
            Name = firstName + lastName
        };

It's also not that efficient with all the nesting of the scalars, however this is how let works: it basicly ruins your query's performance.

I could also try the c.EmployeeCollectionViaOrder.First() call in the projection:

var q = from c in metaData.Customer
        orderby c.Country ascending
        select new
        {
            c.CustomerId,
            c.CompanyName,
            c.Country,
            Employee = c.EmployeeCollectionViaOrder.First()
        };

but this gives a problem too: the related entity is m:n related, over 'order' which isn't part of the in-memory set to merge with. This means that I get two sets: one with CustomerId and one with EmployeeId, which gives a problem: there's no way to match the two.

I'll look into the problem with the let statements.

The let problem runs into an edge case issue which we can't fix at this point. The main issue is that we assign aliases to member info objects on parameters and other elements seen in the query so elements inside a sequence can be referenced outside the sequence (as SQL isn't sequence based but set based. Consider a join between A and B which results in sequence C with properties A and B. The alias given to A is the same as the alias given to property A etc.).

The problem is that the two let statements actually refer to the same memberinfo object, so it gets just 1 alias, and this goes wrong along the way producing the scalar query for the second 'let' statement. It's related to similar problems where you use the same anonymous type in different subqueries in the same query.

We can't fix this at this point as it needs an overhaul to some extend of how aliases are assigned to members and types found in the expression tree and we've postponed this to v3's runtime revision. The workaround I gave you is preferable anyway, sql wise, but you have to do the concatenate in memory (i.e. inside HTML in your case).

hold the presses!

I found a solution to the problem you ran into so:

var q = from c in metaData.Customer
        orderby c.Country ascending
        select new
        {
            c.CustomerId,
            Name = c.EmployeeCollectionViaOrder.First().FirstName + c.EmployeeCollectionViaOrder.First().LastName
        };

now works.

I'll wrap things up (as another linq issue was also solved), and attach a new build to this thread.

(edit) attached.

Hi Frans,

Thanks for the support, sorry about the slow reply.

So I need the new build in order for that to work?

worldspawn wrote:

So I need the new build in order for that to work?

Yes you need the build attached to Frans's post.