Creating Views

Oct 26, 2015 at 4:45 PM
Hello,

Is there any way o can convert something like this to a linq query i can create a view on?

SELECT DISTINCT Entidades.ID as ID, Entidades.Numero as Numero, Entidades.Contribuinte AS Contribuinte,Entidades.Nome as Nome, Situacoes.Designacao AS Situacao,
Entidades.Cliente as Cliente, Entidades.Fornecedor as Fornecedor,

(SELECT TOP 1 ISNULL(SUM(AcumuladosContasSNC.SaldoDevedor),0) - ISNULL(SUM(AcumuladosContasSNC.SaldoCredor),0)
From AcumuladosContasSNC INNER JOIN PrefixosCCEntidades ON PrefixosCCEntidades.ContaID = AcumuladosContasSNC.ContaSNCID
Where AcumuladosContasSNC.ContaSNCID = PrefixosCCEntidades.ContaID And TipoPrefixo = 1 AND Ano <= @Ano
AND PrefixosCCEntidades.EntidadeID = Entidades.ID
Group By Ano Order By Ano Desc) as SaldoCliente,

(SELECT TOP 1 ISNULL(SUM(AcumuladosContasSNC.SaldoCredor),0) - ISNULL(SUM(AcumuladosContasSNC.SaldoDevedor),0)
From AcumuladosContasSNC INNER JOIN PrefixosCCEntidades ON PrefixosCCEntidades.ContaID = AcumuladosContasSNC.ContaSNCID
Where AcumuladosContasSNC.ContaSNCID = PrefixosCCEntidades.ContaID And TipoPrefixo = 2 AND Ano <= @Ano
AND PrefixosCCEntidades.EntidadeID = Entidades.ID
Group By Ano Order By Ano Desc) as SaldoFornecedor,

(Select ISNULL(SUM(Documentos.Saldo),0) From Documentos INNER JOIN
Series ON Documentos.SerieID = Series.Serie
Where Documentos.EntidadeID = Entidades.ID AND Series.TipoMovimento = 2
AND Documentos.DataVencimento < @Data) AS VencidoCliente,

(Select ISNULL(SUM(Documentos.Saldo),0) From Documentos INNER JOIN
Series ON Documentos.SerieID = Series.Serie
Where Documentos.EntidadeID = Entidades.ID AND Series.TipoMovimento = 0
AND Documentos.DataVencimento < @Data) AS VencidoFornecedor

FROM Entidades INNER JOIN PrefixosCCEntidades ON Entidades.ID = PrefixosCCEntidades.EntidadeID LEFT JOIN Situacoes ON Entidades.SituacaoID = Situacoes.ID
Where Entidades.Numero < 999998

Regards
Oct 26, 2015 at 6:47 PM
Hard to say...
Let's start with some simplification/restructuring. I see that the first 2 sub-queries use the same join, and sub-queries 3,4 use another identical join; start with defining these sub-queries as views, and reformulate the query using child views;
I am not so sure how to use sub-queries in SELECT output clause, not even know how to formulate it in LINQ terms; let's start with some research if there's some suggestions from MS for Linq2Sql, then lets see if we can make VITA handle this kind of stuff.
Roman
Oct 26, 2015 at 6:51 PM
ah, what I was thinking, using subquery output in SELECT out fields is possible, look at TestLinqNonQuery_Update test, ordersTotalsQuery. So let's try to start with views for sub-queries, and formulate the output similar to this summarizing query in unit tests.
Oct 27, 2015 at 1:53 AM
Hello,

I am trying to create subquerys to create views but vita is not adding the column name when the column is calculated

var query = from ent in entidades
                    select new
                    {
                        ID = ent.ID,
                        Numero = ent.Numero,
                        Cliente = ent.Cliente,
                        Fornecedor = ent.Fornecedor,
                        Nome = ent.Nome,
                        Contribuinte = ent.Contribuinte,
                        Situacao = ent.Situacao.Designacao,
                        SaldoCliente = entidadesContaCorrente.Where(c => c.TipoPrefixo == TipoPrefixo.Cliente).Sum(f => f.SaldoDevedor - f.SaldoCredor),
                        SaldoFornecedor = entidadesContaCorrente.Where(c => c.TipoPrefixo == TipoPrefixo.Fornecedor).Sum(f => f.SaldoDevedor - f.SaldoCredor),
                    };
I get a error:
Create View or Function failed because no column name was specified for column 8.

The SQL generated is:
CREATE VIEW "dbo"."vEntidadeLista" WITH SCHEMABINDING AS
SELECT ent$."ID" AS "ID", ent$."Numero" AS "Numero", ent$."Cliente" AS "Cliente", ent$."Fornecedor" AS "Fornecedor", ent$."Nome" AS "Nome",
ent$."Contribuinte" AS "Contribuinte", t0$."Designacao" AS "Situacao",
(SELECT SUM((t1$."SaldoDevedor" - t1$."SaldoCredor"))
FROM "dbo"."vEntidadeContaCorrente" t1$
WHERE (t1$."TipoPrefixo" = (1/Cliente/))),
(SELECT SUM((t2$."SaldoDevedor" - t2$."SaldoCredor"))
FROM "dbo"."vEntidadeContaCorrente" t2$
WHERE (t2$."TipoPrefixo" = (2/Fornecedor/))), ent$."SaldoVencido" AS "SaldoVencido"
FROM "dbo"."Entidades" ent$
INNER JOIN "dbo"."Situacoes" t0$ ON t0$."ID" = ent$."SituacaoID"

If i add the column names (SaldoCliente and Saldo Fornecedor)
it creates the view with no problem.

Regards
Oct 27, 2015 at 4:32 AM
ok, got it, will investigate
Oct 30, 2015 at 8:48 PM
should be fixed now, v 1.7.4
Oct 30, 2015 at 10:03 PM
btw, have a look at vBookSales2 query in samples, it is similar to yours, uses subqueries as output columns
Oct 30, 2015 at 10:04 PM
Confirmed.