var result = (from t1 in _dbEntities.Charges
join t2 in _dbEntities.ChargesTypes on t1.ChargesTypeID equals t2.ID
where t1.StudentID == 1033
select new {t2.Amount}).SingleOrDefault();
I want to sum the amount
column how to do that and it should be convert into int
before sum it up
Firstly, you're using SingleOrDefault
which will only select a single value. You want to use Sum
instead. Also, you don't need an anonymous type here. You can just use:
var result = (from t1 in _dbEntities.Charges
join t2 in _dbEntities.ChargesTypes on t1.ChargesTypeID equals t2.ID
where t1.StudentID == 1033
select (int) t2.Ammount).Sum();
Alternatively, you could use the form of Sum
which takes a projection, possibly using a different form of Join
as you only want the t2
value:
var result = _dbEntities.Charges
.Join(_dbEntities.ChargesTypes,
t1 => t1.ChargesTypeID,
t2 => t2.ID,
(t1, t2) => t2)
.Sum(t2 => (int) t2.Ammount);
Or even convert in the join and use the "plain" Sum
afterwards:
var result = _dbEntities.Charges
.Join(_dbEntities.ChargesTypes,
t1 => t1.ChargesTypeID,
t2 => t2.ID,
(t1, t2) => (int) t2.Ammount)
.Sum();
EDIT: If the Ammount
column (which should be Amount
by the way) in the database is an NVARCHAR
then a) change it if you possibly can. Life is always simpler if the database types are appropriate; b) use int.Parse
if you can't:
var result = (from t1 in _dbEntities.Charges
join t2 in _dbEntities.ChargesTypes on t1.ChargesTypeID equals t2.ID
where t1.StudentID == 1033
select int.Parse(t2.Ammount)).Sum();
If that doesn't work, you may need to do the parsing on the .NET side, e.g.
var result = (from t1 in _dbEntities.Charges
join t2 in _dbEntities.ChargesTypes on t1.ChargesTypeID equals t2.ID
where t1.StudentID == 1033
select t2.Ammount)
.AsEnumerable() // Do the rest client-side
.Sum(x => int.Parse(x));
See more on this question at Stackoverflow