New version - DB Views are now supported

Aug 12, 2015 at 10:13 PM
Including materialized and indexed views
Aug 14, 2015 at 1:21 AM
I am having a problem with the views

var artigos = ViewHelper.EntitySet<IArtigo>();
        var acumulados = ViewHelper.EntitySet<IAcumuladosArtigo>();
        var precos = ViewHelper.EntitySet<IDetalheArmazem>();
        var query = from p in precos
                    join art in artigos on p.Artigo.ID equals art.ID into artGroup
                    from a in artGroup.DefaultIfEmpty()
                    join acum in acumulados on a.ID equals acum.Artigo.ID into acumGroup
                    from ac in acumGroup.DefaultIfEmpty()
                    select new
                        ID = a.ID,
                        Designacao = a.Designacao,
                        PV1 = p.PV1SI,
                        PVP1 = p.PV1CI,
                        PV2 = p.PV2SI,
                        PVP2 = p.PV2CI,
                        PV3 = p.PV3SI,
                        PVP3 = p.PV3CI,
                        PV4 = p.PV4SI,
                        PVP4 = p.PV4CI,
                        PV5 = p.PV5SI,
                        PVP5 = p.PV5CI,
                        PCusto = p.PrecoCusto,
                        UPC = p.UltimoPrecoCusto,
                        Codigo = a.Codigo,
                        NrArmazem = p.Armazem.Numero,
                        Saldo = ac.Saldo,
                        PMCP = p.PrecoMedioCusto,
                        Reservas = p.EncomendasCliente,
                        Pedidos = p.EncomendasFornecedor,
                        Descontinuado = a.Descontinuado,
                        Iva = a.Iva.Designacao,
                        MovimentaStock = a.MovimentaStock,
                        CodigoBarras = a.CodigoBarras,
                        Referencia = a.Referencia,
                        Familia = a.Familia.Codigo,
                        SubFamilia = a.SubFamilia.Codigo,
                        Ano = ac == null ? 0 : ac.Ano,
        RegisterView<IArtigoLista>(query, DbViewOptions.Materialized);
I have - Ano = ac == null ? 0 : ac.Ano
because i need to make the colum not nullable but i get this error from VITA
  • $exception {"Linq to SQL translation failed: Conditional expressions not supported in SQL. Expression: acumGroup$.\"ID\" IS NULL ? 0 : acumGroup$.\"Ano\"\r\nPossibly facilities you are trying to use are not supported. \r\nTry to reformulate/simplify the query. Hint: do not use c# functions/methods inside query directly. "} Vita.Data.Linq.Translation.LinqTranslationException
I think it is related to your linq implementation because all examples i see on the net use this.

I have tryed to make colum Ano in IArtigoLista int? but my app simply does not boot with no error.

Aug 14, 2015 at 7:08 PM
yes, this is because of Linq implementation, not directly related to DB Views.
One thing - you do not need all these outer joins, you can just simply select from the 'root' table, and then use dotted property references in the anon object initializer. When you use expression like "LastName = author.User.Person.LastName,', then LINQ will make a join author-user-person automatically. If author.User is nullable, then it will be OUTER join. Next, why don't simply make Ano property of auto-type nullable? simply use the initializer:
Ano = (int?) ac.Ano,
that should work I think.
I will think about implementing support for conditional "?" operator in LINQ, will put it in To-do list.
Aug 14, 2015 at 8:39 PM
If i use this

Ano = (int?) ac.Ano,

The app dos not load, it does not give any error but it freezes with no error.

Aug 14, 2015 at 8:47 PM
at which point it freezes? when running app.Init()/ConnectTo()? I will try a small test here to see what's wrong
Aug 14, 2015 at 8:48 PM
Aug 14, 2015 at 9:41 PM
ok, I found a bug in LINQ, with this (int?) conversion fixing it. Tested with view, seems to be working.
As for freezing, have no clue... can it be because of Materialized - your view tries to actually create stored records, and there are too many?
Pls try the following - remove this troubled Ano column, and remove Materialized flag - see if it starts ok
Aug 14, 2015 at 10:00 PM
The table has no records at all, it is a new database.

the freezing is probabbly bacause of the bug in LINQ because if i do not use the (int?) it creates the view with no ptoblems
Aug 16, 2015 at 6:17 AM
the problem with (int?) value is fixed, there's an extra view AuthorUser, which has UserType property of type 'UserType?', works fine now. The c# conditional operator "?" is not supported yet.