Amiel
Amiel

Reputation: 1

SQL Statement with inner join to LINQ

I need to convert my SQL statement to LINQ.

SELECT        
    dbo.Transactions.TypeRefID, 
    dbo.TransactionItems.ItemRefID, 
    SUM(dbo.TransactionItems.Quantity) AS Qty
FROM
    dbo.TransactionItems 
LEFT OUTER JOIN
    dbo.Transactions ON dbo.TransactionItems.TransactionRefID = dbo.Transactions.TransactionID
GROUP BY 
    dbo.Transactions.TypeRefID, dbo.TransactionItems.ItemRefID
HAVING        
    (dbo.Transactions.TypeRefID = 1) 
    AND (dbo.TransactionItems.ItemRefID = 5)

I tried converting the above statement into LINQ and this is what I've done.

var query = from t in db.Transaction
                        join i in db.TransactionItem on t.TransactionID equals i.TransactionRefID
                        where t.TypeRefID == 1 && i.ItemRefID == 5
                        group i by new
                        {
                            t.TypeRefID,
                            i.ItemRefID
                        } into g
                        select new
                        {
                            TypeRefID = g.Key.TypeRefID,
                            ItemRefID = g.Key.ItemRefID,
                            Quantity = g.Sum(q => q.Quantity)
                        };

When I run my code I get error "System.Linq.Queryable.FirstOrDefault(...) returned null"

I'm using it like this

if (query != null) string qty = query.FirstOrDefault().Quantity.ToString();

The error is called on "query.FirstOrDefault().Quantity.ToString()"

How to avoid this error?

Upvotes: 0

Views: 18

Answers (0)

Related Questions