Hi,
I am sending a model simplified.
See if you can help.
public IEnumerable<DTOCompanyDataWithSumValue>
GetCompanyFinancesWithoutValues(DTOParameter parameter)
{
return Session.QueryOver<Company>(() => companyAlias)
.Where(x => x.Month == parameter.Month)
.And(x => x.Year == parameter.Year)
.WithSubquery.WhereExists(QueryOver.Of<Company>(() =>
companyAlias2)
.Where(e => e.KeyCompany == companyAlias.KeyCompany)
.And(x => x.Month == parameter.Month)
.And(x => x.Year == parameter.Year)
.JoinQueryOver(x => x.Financial,
JoinType.LeftOuterJoin)
.JoinQueryOver(x => x.FinanceRegions, () =>
financesRegionsAlias, JoinType.LeftOuterJoin) //List -- This may contain
records with zero or may not contain records
.Where(Restrictions.IsNull(Projections.Sum<FinanceRegion>(x
=> x.Value)) || Restrictions.Eq(Projections.Sum<FinanceRegion>(x =>
x.Value), 0))
.Select(Projections.Sum<FinanceRegion>(x =>
financeRegionsAlias.Value).WithAlias(() => dtoCompany.SumValue),
Projections.Group(() =>
companyAlias2.KeyCompany).WithAlias(() => dtoCompany.KeyCompany),
Projections.Group(() =>
companyAlias2.CompanyName).WithAlias(() => dtoCompany.CompanyName))
)
.TransformUsing(Transformers.AliasToBean<DTOCompanyDataWithSumValue>())
.List<DTOCompanyDataWithSumValue>().Distinct().ToList();
OR
return Session.QueryOver<Company>(() => companyAlias)
.Where(x => x.Month == parameter.Month)
.And(x => x.Year == parameter.Year)
.WithSubquery.WhereExists(QueryOver.Of<Company>(() =>
companyAlias2)
.Where(e => e.KeyCompany == companyAlias.KeyCompany)
.And(x => x.Month == parameter.Month)
.And(x => x.Year == parameter.Year)
.JoinQueryOver(x => x.Financial,
JoinType.LeftOuterJoin)
.JoinQueryOver(x => x.FinanceRegions, () =>
financesRegionsAlias, JoinType.LeftOuterJoin) //List -- This may contain
records with zero or may not contain records
.Where(Restrictions.Eq(Projections.Sum<FinanceRegion>(x
=> x.Value), 0)) || Restrictions.IsNull(Projections.Sum<FinanceRegion>((x)
=> x.Value)))
.Select(Projections.Sum<FinanceRegion>(x =>
financeRegionsAlias.Value).WithAlias(() => dtoCompany.SumValue),
Projections.Group(() =>
companyAlias2.KeyCompany).WithAlias(() => dtoCompany.KeyCompany),
Projections.Group(() =>
companyAlias2.CompanyName).WithAlias(() => dtoCompany.CompanyName))
)
.TransformUsing(Transformers.AliasToBean<DTOCompanyDataWithSumValue>())
.List<DTOCompanyDataWithSumValue>().Distinct().ToList();
thanks
Em quarta-feira, 6 de maio de 2015 16:38:28 UTC-3, glautter neves escreveu:
>
> Olá pessoal,
>
> Alguém tem alguma dica para montar um queryover com a cláusula Having, mas
> com duas condições?
>
> ex.:
> group by ...
> Having (sum(valor) == 0) *OR* (sum(valor) IS NULL);
>
> Já fiz assim:
> .Where(Restrictions.Eq(Projections.Sum<MinhaClasse>((x) => x.Valor), 0)))
> SQL gerado:
> group by ...
> Having (sum(valor) == 0)
>
> Já fiz assim:
> .Where(Restrictions.Or(Restrictions.Eq(Projections.Sum<MinhaClasse>((x) =>
> x.Valor), 0),
>
> Restrictions.Eq(Projections.Sum<MinhaClasse>((x) => x.Valor), null));
> SQL gerado:
> Erro: Referência de objeto não definida para uma instância de um objeto.
> Porque o valor que estou comparando está nulo.
> Mesmo assim o valor "Restrictions.Or" irá me retornar fora do "Having".
> Será um "And".
>
> O que eu preciso é que a segunda opção
> (Restrictions.Eq(Projections.Sum<MinhaClasse>((x) => x.Valor), null)) me
> traga sum(valor) is null;
>
> Alguém tem alguma dica?
>
> desde já agradeço
>
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.
