Hi, This code worked just fine a little while ago, a few llblgen updates later (afaik) and it's giving me strange results:

var servicesvalues = (from s in metaData.PsServiceValues select new { s.ServiceId });
    
        foreach (var searchValue in proSearch.SearchValues)
        {
            var clonedid = searchValue.ValueID;
                    servicesvalues = (from s0 in servicesvalues
                                      from s1 in metaData.PsServiceValues
                                      where s0.ServiceId == s1.ServiceId
                                      && s1.ValueId == clonedid
                                      select new { s1.ServiceId });
        }

var services = (from s in metaData.PsServices
                        from x in servicesvalues
                        where x.ServiceId == s.ServiceId
                        select s);

var results = ((ILLBLGenProQuery)services).Execute<PsServicesCollection>();

When 2 values are used (ie the loop passes more than once) this error is thrown: Error: Invalid column name 'ValueID'. I can 'fix' that error by using 'select s' instead, but the whole thing is broken as the sql-compiled alias references to each table are actually wrong - hence the initial error.

I can make it work properly by changing the value of s1 to a new name each loop iteration (by hand). Ie, removing the loop and making a long list of the same query but each time changing s1 to s2,s3,s4,s5 etc.

The code is for something a bit like a tagging system. It's looking for values that occur in a table simultaneously (ie, services that have value1 and value2 and value3, but no services that have less than all 3 values)

service value

1 a 1 b 2 a 3 a 3 b

My query would return:

service

1 3

Thanks, Frank.

Hi Frank,

Definitely the problem is in the loop and how the query is constructed. Before we analize your query, please try this code (approximate) coz I think your situation could be resolved in a simpler manner:

List<int> values = new List<int>();
values.Add(31);
values.Add(39);

.....
var q = from sv in metaData.PsServiceValues
         where values.Contains(sv.ValueId)
         group sv by od.ServiceId into g
         select new { ServiceId = g.Key, Ocurrences = g.Count() };

var q2 = from s in metaData.PsServices
         join ssv in q
         on s.ServiceId equals ssv.ServiceId
         where ssv.Ocurrences >= values.Count
         select s;

List<PsServices> services = q2.ToList();

If that code doesn't work for you or you experiment problems, please provide more info (http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=12769)

Hi, thanks for your reply..

I had simplified the example query to illustrate the issue. In the real situation, the internal loop contains multiple switches with varying conditions applied to the base query.

Unfortunately there is really no way to do it outside of a loop.

Plus, it was working fine until recently.. (I had left it for a couple of months, done some routine updates, and restarted on it recently)

Thanks, Frank.

what does 'recently' mean, and can you relate it to a fix we made in the runtime ? (see: changelog browser in the customer area). Have you tried with the latest build ?

I also find it a bit hard to construct a repro case for this.

I thought what you want could be done with the All() method: - return service where not exists a row in servicevalues where valueid not in (list of ids)

e.g. I have employees 1, 2, 3. I want all customers who have orders filed by all three of them:

var q = from c in metaData.Customer
        where c.Orders.All(o=>new[] {1, 2, 3}.Contains(o.EmployeeId.Value))
        select c;

however, this gives wrong results, as when I have the orders with employee 2, 2, 3, it works. Linq to sql gives the same results btw, and technically it is correct but it's not the right path for your particular problem... . I know it's a SQL FAQ but every time I seem to forget the solution...

To have you make progress in this, could you use the Predicate Builder (see first thread in this forum) to append the where clauses instead and see if that works?

Re: Update - I'm sorry, no idea when this happened - I've even checked my SQL 2008 service pack (?ran out of other things to check) - I'm using:

SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll 2.6.8.1114 SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll 2.6.9.729 SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll 2.6.9.511

I'm looking into that 'All' method right now, perhaps it's a more elegant solution than the multiple joins.

Re: Predicate class - I could, but the problem appears in the 'aliases' for the joins. Ie when looping and joining, you can only use the 'alias' one time:

servicesvalues = (from s1 in metaData.PsServiceValues from s0 in servicesvalues where s0.ServiceId == s1.ServiceId && s1.ValueId == clonedid select new { s1.ServiceId }); :endloop:

The code would work if s1 could be magically changed to s2,s3,s4 etc on each iteration. This definitely worked before, though as I said I can't seem to recreate it now. Perhaps a .NET update changed all this?

ie: When i write out the queries in sequence without a loop, and change the s1 each time - the final query executes as expected.

Thanks for your time on this!

It could be that the foreach loop is optimized to re-use object instances created inside the loop, as they're exported outside the loop as the query is executed there. I conclude that because if you write out the loop the query runs as expected.

if you refactor the loops contents in a method, would that work? (just for testing)

Great idea, but no dice..

Same error, no difference.

-Refactored it out of the loop into it's own method -tried to use a class to project the ID (not anony) -tried to request a new XData.Linq.LinqMetaData() inside the refactored method and used that to build the query to add -Used a different variable for building the query in the refactored method

All failed in the same way..

I think it's this: servicesvalues = (from s0 in servicesvalues...

which is done inside the loop, and as 'servicevalues' is changing along the way, it's also changing INSIDE the query. I'm constructing a test now to check whether this is indeed the reason.

(edit) that isn't the reason. Looking into it.

(edit) I found the cause of the error you run into, working on a fix for that now. I tried to rewrite it with normal joins (so for each value an inner join, which is more efficient), and no anonymous types (as these are unnecessary) and ran into a related problem, which I'll also fix.

I tracked it down to a wrong alias being set in an ON clause field (which is the where clause for cross joins) but the lines (2) affected haven't been changed since march 2008. It might be that the alias set is actually now getting a value instead of not having a value at all previously which causes the problem. I'll fix it and see if that indeed solves your problem.

Thank Otis, Will I be able to download these updates when you're done?

hitchhiker wrote:

Thank Otis, Will I be able to download these updates when you're done?

Yes. I can't get a grip on the issue you must have ran into as well (namely a join with select s.ServiceId instead of new { s.ServiceId}, as this is one very strange thing with Linq's design: the 'where' clause which binds entity set with value list refers to a 'member', named s0. This member is a member of the anonymous type of the resultprojection of the join. So the where refers to <somealias>.s0==<someotheralias>.ServiceId. However, 's0' isn't an alias in the set where is referring to, as that 's0' is only known in the join And is only detectable as needed when the where (which is around the join) is seen. I.o.w.: a hodgepodge of references all over the place in the expression tree which aren't controllable I've tried all morning with various setups to overcome this but it always fails one way or the other simply because the info it needs is elsewhere and unreachable (outside its scope in the tree).

I can fix it for new {s.ServiceId}, which I'm currently working on. I'll attach the build to this thread when I'm done

I also thought of a different way to do this btw, without joins: using Contains. (using 'join' runs into the same issue, but is also more efficient). I'll write them down when I'm done.

The multiple times that a join is added, the projection expression is always the same, so the type the projection is done into is always the same. This means that when a 'where' is referring to a side in the join, it refers to one of the two members of the projection result anonymous type. However for all nested joins (nested into eachother) that type is always the same, so the alias assigned to it is also always the same. This leads to a problem with queries added later on, as they can't refer to the nested table (as it's not the one they're referring to) and get the alias of the nested query...

What a nightmare.

I'll try to see if I can fix it, otherwise to see if I can write it with contains to get you along with your work. In any case, writing this query with our native API does work, for the case you might run into a deadline or something.

Ok, I found a way to do this, with joins. My order query is as follows:

var customers = from c in metaData.Customer select c;
var empIds = new[] { 1, 2, 3 };
foreach(var empId in empIds)
{
    var cloneid = empId;
    customers = (from c in customers
                 where c.Orders.Any(o => o.EmployeeId.Value == cloneid)
                 select c);
}
var q = customers.OrderBy(c=>c.CustomerId);

so for you this would become:

var services = from s in metaData.PsServices select s;
foreach (var searchValue in proSearch.SearchValues)
{
    var clonedid = searchValue.ValueID;
    services = from s in services
               where s.ServiceValues.Any(sv=>sv.ValueId==clonedid)
               select s;
}

I'll attach the latest linq provider build so you can test this out.

Fantastic, elegant, works perfectly!

I used the solution you presented in the post above this, in conjunction with the updated .dll - Additionally I managed to incorporate a switch(){} within the loop that allows for infinite variation in each iteration's constraints.

Thanks so much Otis! Frank.

Well, it took almost all day, but I'm glad it works!