Error in linq query

Jan 6, 2016 at 1:17 AM
I have this query

var query = _session.EntitySet<IDocumento>();
        query = query.Where(c => c.DataDocumento == Caixa.DataCaixa && c.Assinado && c.Saldo == 0 && c.Serie.Saft == TipoSaft.FS);
        query = query.Where(c => c.Pagamentos.Any(f => f.Caixa.ID == Caixa.ID));
        var resultado = query.ToList();
When i execute it my application exit's
If i remove the last part

query = query.Where(c => c.Pagamentos.Any(f => f.Caixa.ID == Caixa.ID));

it works ok, but i cannot filter the 'Pagamentos' list of the IDocumento

Any sugestion?

Happy New Year to you.
Regards.
Coordinator
Jan 6, 2016 at 5:57 AM
well... what does this mean - app exits? no exception? Put try/catch around and see what's the exception.
One thing to try, instead of Any use Contains; also look at TestLinqContains unit test, there are different queries with subqueries there. Overall, I remember beating much more on 'Contains' implementation than "Any", so it might work if you can rewrite it
Happy New year!
Jan 6, 2016 at 10:53 PM
The error is

Linq to SQL translation failed: O operador binário Equal não está definido par os tipos 'System.Nullable`1[System.Guid]' e 'System.Guid'.
Possibly facilities you are trying to use are not supported.
Try to reformulate/simplify the query. Hint: do not use c# functions/methods inside query directly.

I canot find a way to do the same thing with Contains.

Regards
Coordinator
Jan 6, 2016 at 11:00 PM
Caixa.ID is a nullable column? how's that possible, looks like Primary Key, should not be nullable.
Anyway, try using Caixa.ID.Value in expression.
Or f.Caixa is nullable? then use f.Caixa == Caixa
Jan 6, 2016 at 11:04 PM
Caixa.ID is not nullable nor is f.Caixa is nullable
Coordinator
Jan 7, 2016 at 4:44 PM
Ok, let's investigate this. It's really hard to guess what's wrong from the info you provided so far.
What about removing the first 'where' clause - will it still fail? In general, try to find minimum expression that fails, with just one WHERE. Then pls send me full call stack from the exception. You can do 'exc.ToLogString()' - this will include all, save it in a variable, stop in debugger, and copy-paste it into email. Also pls include definitions of all entities involved.

Rewriting with Contains - how about:
var subQuery = session.EntitySet<IPagamento>().Where(p=>p.Caixa == Caixa).Select(p=>p.Document.Id)); //returns IDs of documents
var query = query.Where(d => subQuery.Contains(d.Id));

or something like that, so much as I could guess from your code. The idea is to create a subquery first for this 'Any' condition. I also think with this subquery the resulting SQL would be more efficient

So please reduce the query and send me all info, let's crack it
Roman
Jan 7, 2016 at 7:15 PM
With the subquery and Contains it works very well.

Thanks for your help.

The more i use VITA more i love it :)
Coordinator
Jan 7, 2016 at 9:05 PM
thanks, love to hear it works, but waaaaait a second plzzz.. let's figure out what was wrong with the old query. Can you please spend some time with it, reduce it as I described to get minimal failing version, and then send me the full stack of exc, with some entity definitions? I need to fix the problem, but I need some way to repro it here
thanks in advance
Jan 7, 2016 at 9:34 PM
Sent you a message.
Thanks
Coordinator
Jan 8, 2016 at 7:01 AM
done, reproduced on Books. will issue a patch. thanks!