تبدیل Derived Table در Tsql به LINQ

[Salah Sanjabian]

سلام خدمت دوستان . چطور میتونم این کوئری رو تبدیل به LINQ کنم؟

/SELECT Type='OutGo',Name='SaledProductCost', Debtor= SUM(Temp.Value),Creditor=NULL
FROM
(

SELECT SUM(X.Value) FROM
(SELECT P.FirstValue*P.FirstPurchasePrice *Units.Ratio As VALUE
FROM Products P INNER JOIN
Units ON P.MinUnitRef=Units.ID
)X

UNION ALL

SELECT SUM(RealSum)
FROM Factors WHERE TypeRef='Buy'
UNION ALL

SELECT -SUM(RealSum)
FROM dbo.Factors WHERE TypeRef='RevertBuy'
UNION ALL

SELECT -SUM(VALUE*PurchasePrice)
FROM dbo.Products WHERE Value>0

) Temp(Value)

یه Derived Table رو چطور تو LINQ باید پیاده کرد مشکلم Derived Table بود این کدی هست که من نوشتم

(
(from Factors in contextDB.Factors
where Factors.TypeRef == "Buy"
select new { Total = Factors.RealSum }
)
.Concat
(

from Factors in contextDB.Factors
where Factors.TypeRef == "RevertBuy"
select new { Total = -Factors.RealSum }
).Concat
(

from Products in contextDB.Products
where Products.Value > 0
select new { Total = -(Products.Value * Products.PurchasePrice) }
).Concat
(
from Products in contextDB.Products
join Units in contextDB.Units on Products.MinUnitRef equals Units.ID
select new { Total = Products.FirstValue * Products.PurchasePrice * Units.Ratio }
))
.Sum(s => s.Total);

[Mahmoud.Afrad]

var temp = (
from Factors in contextDB.Factors
where Factors.TypeRef == "Buy"
select new { Total = Factors.RealSum }
)
.Concat
(
from Factors in contextDB.Factors
where Factors.TypeRef == "RevertBuy"
select new { Total = -Factors.RealSum }
)
.Concat
(
from Products in contextDB.Products
where Products.Value > 0
select new { Total = -(Products.Value * Products.PurchasePrice) }
)
.Concat
(
from Products in contextDB.Products
join Units in contextDB.Units on Products.MinUnitRef equals Units.ID
select new { Total = Products.FirstValue * Products.PurchasePrice * Units.Ratio }
);

var result = new
{
Type = "OutGo",
Name = "SaledProductCost",
Debtor = temp.Sum(i => i.Total),
Creditor = (decimal?)null
};

[Salah Sanjabian]

ممنون از پاسختون .
الان نتیجه (result)رو باید با این کوئری Union کنم.نشد که Concat کنم

(from Cashs in contextDB.CashBoxes
group Cashs by Cashs.ID into G
select new
{
Type = "Asset",
Name = "CashAmounts",
Debtor = G.Sum(x => x.Amount),
Creditor = (decimal?)null
}
).concat(result)

[Salah Sanjabian]

مشکل از Creditor Property نبود چون من از اول هم Creditor رو اضافه کرده بودم و این Error رو داشتم

Error 1 'System.Linq.IQueryable<AnonymousType#1>' does not contain a definition for 'Concat' and the best extension method overload 'System.Linq.Queryable.Concat<TSource>(System.Linq.IQueryable<TSource>, System.Collections.Generic.IEnumerable<TSource>)' has some invalid arguments

Error 2 Argument 2: cannot convert from 'AnonymousType#1' to 'System.Collections.Generic.IEnumerable<AnonymousType#1>'

[Salah Sanjabian]

مشکل اینه که نمیشه یه کلاس بی نام رو که هنوز لیست نشده و جز Collection نیست رو Concat کرد اینجوری میشه حلش کرد

class Helper
{
public string Type{get;set;}
public string Name { get; set; }
public decimal? Debtor { get; set; }
public decimal? Creditor { get; set; }
}



IQueryable<Helper> Query = (from Cashs in contextDB.CashBoxes
group Cashs by Cashs.ID into G
select new Helper
{
Type = "Asset",
Name = "CashAmounts",
Debtor = G.Sum(x => x.Amount),
Creditor = (decimal?)null
})
.Concat
(
from Accounts in contextDB.Accounts
group Accounts by 0 into G
select new Helper
{
Type = "Asset",
Name = "CashAmounts",
Debtor = (decimal?)G.Sum(x => x.CurrentSupply),
Creditor = (decimal?)null
}
)



decimal Temp = (decimal)

(

(from Factors in contextDB.Factors
where Factors.TypeRef == "Buy"
select new { Total = Factors.RealSum }
)
.Concat
(

from Factors in contextDB.Factors
where Factors.TypeRef == "RevertBuy"
select new { Total = -Factors.RealSum }
).Concat
(

from Products in contextDB.Products
where Products.Value > 0
select new { Total = -(Products.Value * Products.PurchasePrice) }
).Concat
(
from Products in contextDB.Products
join Units in contextDB.Units on Products.MinUnitRef equals Units.ID
select new { Total = Products.FirstValue * Products.PurchasePrice * Units.Ratio }
))
.Sum(s => s.Total);



List<Helper> SList = Query.ToList();
SList.Add(new Helper { Type = "OutGo", Name = "SaledProductCost", Debtor = Temp, Creditor = 0 });

[Salah Sanjabian]

دوست عزیز این کار امکان پذیر نیست و باید از روشی که تو پست 6 گفتم استفاده کرد چون نوع Var مشخص نیست و من کد شما رو امتحان کردم و این خطا رو دارم ومنطقی هم هست چون نمیتونه یه کلاس بی نام رو به یه لیست اضافه کنم

Error 1 Cannot implicitly convert type 'void' to 'System.Linq.IQueryable<object> -



var temp = (
from Factors in contextDB.Factors
where Factors.TypeRef == "Buy"
select new { Total = Factors.RealSum }
)
.Concat
(
from Factors in contextDB.Factors
where Factors.TypeRef == "RevertBuy"
select new { Total = -Factors.RealSum }
)
.Concat
(
from Products in contextDB.Products
where Products.Value > 0
select new { Total = -(Products.Value * Products.PurchasePrice) }
)
.Concat
(
from Products in contextDB.Products
join Units in contextDB.Units on Products.MinUnitRef equals Units.ID
select new { Total = Products.FirstValue * Products.PurchasePrice * Units.Ratio }
);

var result = new
{
Type = "OutGo",
Name = "SaledProductCost",
Debtor = temp.Sum(i => i.Total),
Creditor = (decimal?)null
};



query = (from Cashs in contextDB.CashBoxes
group Cashs by Cashs.ID into G
select new
{
Type = "Asset",
Name = "CashAmounts",
Debtor = G.Sum(x => x.Amount),
Creditor = (decimal?)null
}).Concat


(
from Accounts in contextDB.Accounts
group Accounts by 0 into G
select new
{
Type = "Asset",
Name = "CashAmounts",
Debtor = (decimal?)G.Sum(x => x.CurrentSupply),
Creditor = (decimal?)null
}
).ToList().Add(result);

[Mahmoud.Afrad]

درسته اعضای لیست و همچنین result بدون نوع هستند اما پراپرتی ها همنام و همنوع و هم تعداد هست پس میتونی به صورت زیر اضافه کنی:

var lst = (from Cashs in contextDB.CashBoxes
group Cashs by Cashs.ID into G
select new
{
Type = "Asset",
Name = "CashAmounts",
Debtor = G.Sum(x => x.Amount),
Creditor = (decimal?)null
}
).ToList();
lst.Add(result);

[Salah Sanjabian]

ممنون ولی کد شما با این کد چه فرقی داشت؟ اول tolist کردین بعد Add ولی من یه باره این کارو کردم

(
from Accounts in contextDB.Accounts
group Accounts by 0 into G
select new
{
Type = "Asset",
Name = "CashAmounts",
Debtor = (decimal?)G.Sum(x => x.CurrentSupply),
Creditor = (decimal?)null
}
).ToList().Add(result);

[Mahmoud.Afrad]

ممنون ولی کد شما با این کد چه فرقی داشت؟ اول tolist کردین بعد Add ولی من یه باره این کارو کردم

(
from Accounts in contextDB.Accounts
group Accounts by 0 into G
select new
{
Type = "Asset",
Name = "CashAmounts",
Debtor = (decimal?)G.Sum(x => x.CurrentSupply),
Creditor = (decimal?)null
}
).ToList().Add(result);

یه اشتباه لپی بود! چون اول لیست رو باید بسازید بعد بهش اضافه کنید و همچنین اون خطا میگه متد Add مقدار بازگشتی نداره(void هستش) پس نمیتونه مساوی query قرار بگیره. به همین دلیل باید در یک خط جدا add بشه.