CODEWITHSUNDEEP
c#linqlinq-to-sqlselect
Simon Kiely
Simon Kiely

Reputation: 6040

Using Linq to SQL is it possible to retrieve two values and subtract them?

I am populating a class using Linq to SQL.

What I am trying to do is query my database, return two integer values and subtract the two values from each other, producing the result, but I can't think of a smart way to do it.

What can I do in this case ?

If it is not clear, , then this psuedocode implementation should clarify what functionality I wish for :

DECLARE @currentVal INT, @previousVal INT
@currentVal = SELECT VALUE
FROM Table1
WHERE Date = CURRDATE()

@previousVal = SELECT VALUE
FROM Table1
WHERE Date = MIN(Date)

RETURN @currentVal - @previousVal

But in Linq to SQL, (from o in context.Table1 where Date = currentDate select Value), how can I subtract the other value from this? Is this possible?

Upvotes: 2

Views: 1357

Answers (4)

AD.Net
AD.Net

Reputation: 13399

var currentVal = context.Table1.FirstOrDefault(t=>t.Date == DateTime.Now.Date);
var previousVal = context.Table1.FirstOrDefault(t=>t.Date == context.Table1.Min(d=>d.Date));

var result = currentVal - previousVal;

Or

from d in context.Table1
let prevVal = context.Table1.FirstOrDefault(t=>t.Date == context.Table1.Min(d=>d.Date));
where d.Date == DateTime.Now.Date
return new { d - prevVal };

Upvotes: 0

George Aletras
George Aletras

Reputation: 106

Something like this would work to keep it into one trip to the db (Keep in mind this assumes that only two results will be returned):

int[] values = (from o in context.Table1 
    where Date = currentDate || Date = context.Table1.Min(x => x.Date)
    order by Date descending
    select value).ToArray();

return values[0] - values[1];

Upvotes: 0

Bob Vale
Bob Vale

Reputation: 18474

Why not simply do a cross join

var query=
  from a in Table1 where a.Date == DateTime.Now
  from b in Table1 where b.Date == Table1.Min(c=>c.Date)
  select a.Amount - b.Amount;

var result=query.First();

Upvotes: 0

Adam Houldsworth
Adam Houldsworth

Reputation: 64477

I'd stick to having it as a broken out set of queries, because you can then test if the values were actually returned or not and handle the case where too many values are returned:

var currentValResults = (from row in rows 
                         where row.Date == DateTime.Now 
                         select row.Value)
                        .ToArray();

var previousValResults = (from row in rows 
                          let minDate = rows.Min(r => r.Date)
                          where row.Date == minDate 
                          select row.Value)
                         .ToArray();

if (currentValResults.Length == 1 && previousValResults.Length == 1)
{
    var diff = currentValResults[0] - previousValResults[0];
}
else
{
    // Error condition?
}

Putting it all into a giant linq statement makes too many assumptions (or at least, my implementation does).

Upvotes: 2

Related Questions