I have the following SQL query:
SELECT
a.DocumentNumber, a.ItemNumber,
SUM(a.ItemAmount) AS ItemAmount
FROM
(SELECT
SopNumbe AS DocumentNumber, ITEMNMBR AS ItemNumber,
XTNDPRCE AS ItemAmount
FROM
dbo.SOP30300
WHERE
SOPNUMBE = '8403017 '
AND XTNDPRCE <> 0
UNION ALL
SELECT
LAPTODNM AS DocumentNumber, LITMNMBR AS ItemNumber,
-1 * ta.APPTOAMT AS ItemAmount
FROM
dbo.RM20201 a
LEFT JOIN
dbo.TRM20201 ta ON a.APFRDCNM = ta.LAPFRDNM
AND a.APTODCNM = ta.LAPTODNM
WHERE
ta.LAPTODNM = '8403017 ') a
GROUO BY
a.DocumentNumber, a.ItemNumber
HAVING
SUM(a.ItemAmount) > 0
Here is the linq I am trying
var result = (from sop in _librgContext.Sop30300
where sop.Sopnumbe == "8403017" && sop.Xtndprce != 0
select new InvoiceSummary
{
DocumentNumber = sop.Sopnumbe,
ItemNumber = sop.Itemnmbr,
ItemAmount = sop.Xtndprce
})
.Union(
from rm in _librgContext.Rm20201
join ta in _librgContext.TRM20201 on new { Key1 = rm.ApplyFromDocumentNumber, Key2 = rm.ApplyToDocumentNumber } equals new { Key1 = ta.Lapfrdnm, Key2 = ta.Laptodnm } into joinResult
from ta in joinResult.DefaultIfEmpty()
where ta != null && ta.Laptodnm == "8403017" // Added null check for ta
// && rm.GLPOSTDT <= CutoffDate // Uncomment if @CutoffDate is a parameter
select new InvoiceSummary
{
DocumentNumber = ta.Laptodnm, // Adjusted to use LAPTODNM from ta
ItemNumber = ta.Litmnmbr,
ItemAmount = -1 * (ta != null ? rm.ApplyToAmount : 0)
})
.GroupBy(a => new { a.DocumentNumber, a.ItemNumber })
.Where(g => g.Sum(a => a.ItemAmount) > 0)
.Select(g => new InvoiceSummary
{
DocumentNumber = g.Key.DocumentNumber,
ItemNumber = g.Key.ItemNumber,
ItemAmount = g.Sum(a => a.ItemAmount)
});
Got the following on debugging Unable to translate set operation when matching columns on both sides have different store types.
Ok finally I am able to do this which is working as expected as of now
var result1 = (
from sop in _librgContext.Sop30300
where sop.Sopnumbe == sopNumber && sop.Xtndprce != 0
select new InvoiceSummary
{
DocumentNumber = sop.Sopnumbe,
ItemNumber = sop.Itemnmbr,
ItemAmount = sop.Xtndprce
}).ToList(); // Execute the query to bring the data into memory
var result2 = (
from rm in _librgContext.Rm20201
join ta in _librgContext.TRM20201
on new
{
rm.ApplyFromDocumentNumber,
rm.ApplyToDocumentNumber
} equals new
{
ApplyFromDocumentNumber = ta.Lapfrdnm,
ApplyToDocumentNumber = ta.Laptodnm
} into joinResult
from ta in joinResult.DefaultIfEmpty()
where ta != null && ta.Laptodnm == sopNumber
select new InvoiceSummary
{
DocumentNumber = ta.Laptodnm, // Removed ToString() conversion
ItemNumber = ta.Litmnmbr, // Removed ToString() conversion
ItemAmount = -1 * (ta != null ? rm.ApplyToAmount : 0)
}).ToList(); // Execute the query to bring the data into memory
var combinedResult = result1.Concat(result2)
.GroupBy(a => new { a.DocumentNumber, a.ItemNumber })
.Where(g => g.Sum(a => a.ItemAmount) > 0)
.Select(g => new InvoiceSummary
{
DocumentNumber = g.Key.DocumentNumber,
ItemNumber = g.Key.ItemNumber,
ItemAmount = g.Sum(a => a.ItemAmount)
});
var finalResult = combinedResult.ToList();