Problemas With LINQ

Jul 30, 2015 at 1:17 AM
I hade this query in my application

var artigos = _session.EntitySet<IArtigo>();
        var precos = _session.EntitySet<IDetalheArmazem>();

        var query = artigos.Join(precos, a => a.ID, p => p.Artigo.ID, (a, p) => new { a, p });
        query = query.Where(c => c.a.Descontinuado == descontinuados && c.p.Armazem.Numero == armazem.Numero);

        if (familia != null) query = query.Where(c => c.a.Familia.Codigo == familia.Codigo);
        if (subFamilia != null) query = query.Where(c => c.a.SubFamilia.Codigo == subFamilia.Codigo);
        if (!string.IsNullOrEmpty(codigo)) query = query.Where(c => c.a.Codigo.StartsWith(codigo));
        if (!string.IsNullOrEmpty(referencia)) query = query.Where(c => c.a.Referencia.StartsWith(referencia));
        if (!string.IsNullOrEmpty(codigoBarras)) query = query.Where(c => c.a.CodigoBarras.StartsWith(codigoBarras));

        query = texto.Aggregate(query, (current, s) => current.Where(c => c.a.Designacao.Contains(s)));

        return query.Select(c => new ArtigoLista
        {
            Designacao = c.a.Designacao,
            Codigo = c.a.Codigo,
            Descontinuado = c.a.Descontinuado,
            ID = c.a.ID,
            MovimentaStock = c.a.MovimentaStock,
            PCusto = c.p.PrecoCusto,
            PVenda1CI = ivaIncluido ? c.p.PV1CI : c.p.PV1SI,
            PVenda2CI = ivaIncluido ? c.p.PV2CI : c.p.PV2SI,
            PVenda3CI = ivaIncluido ? c.p.PV3CI : c.p.PV3SI,
            PVenda4CI = ivaIncluido ? c.p.PV4CI : c.p.PV4SI,
            PVenda5CI = ivaIncluido ? c.p.PV5CI : c.p.PV5SI,
            PMCP = c.p.PrecoMedioCusto,
            Pedidos = c.p.EncomendasFornecedor,
            Reservas = c.p.EncomendasCliente,
            UPC = c.p.UltimoPrecoCusto,
            Saldo = c.p.Existencias
        }).ToList();
After the last update it now gives me a error:

An unhandled exception of type 'Vita.Data.Linq.Translation.LinqTranslationException' occurred in Vita.dll

Additional information: Linq to SQL translation failed: Conditional expressions not supported in SQL. Expression: @P2 ? p$."PV1CI" : p$."PV1SI"

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.

It worked perfecly before, so problably some of the LINQ changes you made are causing this, any sudgestions?

Thanks
Coordinator
Jul 30, 2015 at 6:56 AM
yes, looks like result of latest refactoring, and this is on purpose - sorry for trouble - but my reasoning is below.
You can easily fix it simply by creating two separate 'return/select/new' branches, but I think you know about this. One note about all these multiple 'if's' - there's a bit more compact way, using predicate extensions - look at BookSearchHelper class.
Now about what changed and why. At the last phase of LINQ->SQL translation the engine cuts the operations that must be done in SQL and those that are done in CLR when reading result values. The old algorithm was preferring pushing more to CLR side, that's why your query was working, the '?' operator was evaluated when after reading data returned from SQL.
However, when I started preparing support for DB views, it saw some bad consequences of this behavior. Like when you have 'col.A + col.B' as output expression, the engine was translating it into SQL returning separate columns A and B, and performing addition in CLR. But that's not what you want when you build for DB View! So I switched the engine to push more operations at SQL level, so that SQL reflects more closely the original expression. One possibility is to be flexible and change strategies depending on query's purpose. But I think this would be bad, as I think better have a consistent SQL generation. So I just switched the translation behavior.
One expected consequence is that it discourages/forbids some borderline queries like you have. Which I think is not so bad, cause using borderline cases should be discouraged anyway. Not every Linq2Object query can be translated into SQL, this is a fact, the line should be drawn somewhere, now it moved and not-so-clear queries are outright rejected.
Hope this explains it, and I hope it's not a big deal for you to fix the query.
Jul 30, 2015 at 2:30 PM
That explains it.

Not a big deal.

Regards
Aug 5, 2015 at 1:46 AM
I have another problem, with Date Fields

query = _session.EntitySet<IMarcacao>().Where(c=>c.DataMarcacao.Date == data.Date).ToList();

produces this SQL:
exec sp_executesql N'SELECT "Id", "Data", "Hora", "TrabalhosRealizar", "Avisar", "Avisado", "Sms", "Email", "Concluido", "ClienteID", "MotoID", "MarcadoPor_Id"
FROM "dbo"."Marcacao"
WHERE ("Data" = @P0)',N'@P0 datetime2(7)',@P0='2015-08-05 00:00:00'

It is sending the Time also so the results are not what i need.
Any way to resolve this?

Regards
Coordinator
Aug 5, 2015 at 2:31 AM
but why then c.DataMarcacao.Date ?? taking 'Date'from DateTime cuts off time, so it appears the SQL does it correctly cutting off time from parameter.
And why is this 'sp_executesql' there? I don't remember putting anything like that in linq engine
Aug 5, 2015 at 2:40 AM

That is from sql profiler

Ruben Alves




On Tue, Aug 4, 2015 at 7:31 PM -0700, "rivantsov" <[email removed]> wrote:

From: rivantsov

but why then c.DataMarcacao.Date ?? taking 'Date'from DateTime cuts off time, so it appears the SQL does it correctly cutting off time from parameter.
And why is this 'sp_executesql' there? I don't remember putting anything like that in linq engine
Coordinator
Aug 5, 2015 at 2:45 AM
still, DataMarcacao.Date - isn't is cutting off time, and taking date only, so == is true only if we cut-off time in param value?
Aug 5, 2015 at 2:48 AM

I have tried several whays and the time is always included in the query

Ruben Alves




On Tue, Aug 4, 2015 at 7:45 PM -0700, "rivantsov" <[email removed]> wrote:

From: rivantsov

still, DataMarcacao.Date - isn't is cutting off time, and taking date only, so == is true only if we cut-off time in param value?
Coordinator
Aug 5, 2015 at 2:55 AM
then can you pls give a definition of entity and script for underlying table?
Coordinator
Aug 6, 2015 at 8:54 PM
hey, did you solve your problem?
Aug 7, 2015 at 11:27 AM
No,

What is the correct way to compare only the date and ignore the time using vita?

Regards
Coordinator
Aug 7, 2015 at 5:37 PM
There's DateOnly attribute for DateTime property - this results in automatic cut-off of time portion in any updated values. Then when querying you just use Date property for values:
where ent.DateProp == someDateTime.Date

Can you please post definition (c#) of the entity you have with date property, and also SQL definition of the table (Script table as... in Management Studio), and then the query you try to run
Aug 8, 2015 at 1:11 PM
The entity

[Entity]
public interface IMarcacao
{
    [PrimaryKey, Auto]
    Guid Id { get; set; }
    [EntityRef("MotoID")]
    IMoto Moto { get; set; }
    [EntityRef("ClienteID")]
    ICliente Cliente { get; set; }
    [Nullable]
    string Telemovel { get; set; }
    [Nullable]
    string EmailEnvio { get; set; }
    DateTime Data { get; set; }
    [Nullable]
    DateTime? Hora { get; set; }
    [Unlimited, Nullable]
    string TrabalhosRealizar { get; set; }
    bool Avisar { get; set; }
    bool Avisado { get; set; }
    bool Sms { get; set; }
    bool Email { get; set; }
    bool Concluido { get; set; }
    [Nullable]
    IUtilizador MarcadoPor { get; set; }
    IList<IRegistoMarcacao> Registos { get; set; }
}
CREATE TABLE [dbo].[Marcacao](
[Id] [uniqueidentifier] NOT NULL,
[Data] [datetime2](7) NOT NULL,
[Hora] [datetime2](7) NULL,
[TrabalhosRealizar] [nvarchar](max) NULL,
[Avisar] [bit] NOT NULL,
[Avisado] [bit] NOT NULL,
[Sms] [bit] NOT NULL,
[Email] [bit] NOT NULL,
[Concluido] [bit] NOT NULL,
[ClienteID] [uniqueidentifier] NOT NULL,
[MotoID] [nvarchar](50) NOT NULL,
[MarcadoPor_Id] [uniqueidentifier] NULL,
[Telemovel] [nvarchar](50) NULL,
[EmailEnvio] [nvarchar](50) NULL,
CONSTRAINT [PK_Marcacao] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The query i am executing is:

var query = _session.EntitySet<IMarcacao>().Where(c=>c.Data.Date == dateVar.Value);

Regards
Coordinator
Aug 9, 2015 at 7:21 PM
Ok, I got it. Your actual problem is not that LINQ is sending time part of parameter (which is all zero). The problem is that it does not take DATE part of the column. This is a bug (incomplete implementation in fact). I will fix it in next push. it will be something like
WHERE (DATE(datCol) = @P1)
However, I still do not recommend using this facility. I'm afraid using DATE function over column value would cut-off using index(es), and result in full scan. So better arrangement is the following. If the Date column is intended to be used as Date-only value, then add [DateOnly] attribute on property. Then it should work with your query.
The DateOnly attr results in automatic nullifying of time part when saving the entity, so comparing col value with Date part of c# datetime should work.
In general I would recommend treating Datetime value the same way as double/float - the equal operation is unreliable, so rather than comparing for equal, check for daterange (between one second before and after)
Roman
Aug 14, 2015 at 8:13 PM
Another problem with VITA

if i use Any() like this

if (!session.EntitySet<IAcumuladosArtigo>().Any(c => c.Ano == ano))

It gets translated to this

SELECT (COUNT_BIG(*)) > 0
FROM "dbo"."AcumuladosArtigos"
WHERE ("Ano" = @P0)',N'@P0 int',@P0=2015

and VITA gives a error: Incorrect syntax near '>'.

Regards
Coordinator
Aug 15, 2015 at 3:58 PM
yeah, I reproduce it. Working on a fix
Coordinator
Aug 16, 2015 at 5:14 AM
Fixed, in the latest push