Creating Views

Oct 26, 2015 at 3: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
Coordinator
Oct 26, 2015 at 5: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
Coordinator
Oct 26, 2015 at 5: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 12: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
Coordinator
Oct 27, 2015 at 3:32 AM
ok, got it, will investigate
Coordinator
Oct 30, 2015 at 7:48 PM
should be fixed now, v 1.7.4
Coordinator
Oct 30, 2015 at 9: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 9:04 PM
Confirmed.