Linq to sql, make one db call instead of three

Question

Hi, I calculate user points based on the priorities they received for the messages they sent, the events they sent, and the comments that the points received -

public int GetUserPoints(string userName)
{
    int? postPoints = db.Posts.Where(p => p.Aspnet_User.UserName == userName).Sum(b => (int?)b.UpVotes);
    int? eventPoints = db.Events.Where(p => p.Aspnet_User.UserName == userName).Sum(b => (int?)b.UpVotes);
    int? commentPoints = db.Comments.Where(p => p.Aspnet_User.UserName == userName).Sum(c => (int?)c.UpVotes - (int?)c.DownVotes);

    return (postPoints.HasValue ? postPoints.Value : 0) + (eventPoints.HasValue ? eventPoints.Value : 0) + (commentPoints.HasValue ? commentPoints.Value / 5 : 0);
}

Im making 3 separate db calls to achieve this. can i do this in one?

Answer 1

If you really only need one call and still use LINQ to SQL to build the query, you can use:

var sum = (from p in db.Posts where p.Aspnet_User.UserName == userName select p.UpVotes).Concat
                (from e in db.Events where e.Aspnet_User.UserName == userName select e.UpVotes).Concat
                (from c in db.Comments where c.Aspnet_User.UserName == userName select (c.UpVotes - c.DownVotes)).Sum()

Answer 2

- SQL Server, ( , ..), , .

Linq-to-SQL .

, , , :

CREATE PROCEDURE dbo.GetUserPoints(@UserName VARCHAR(50))
AS BEGIN
   DECLARE @UserID UNIQUEIDENTIIFIER

   SELECT @UserID = ID FROM dbo.ASPNET_Users WHERE UserName = @UserName

   DECLARE @PostPoints INT
   DECLARE @EventPoints INT
   DECLARE @CommentPoints INT

   SELECT @PostPoints = SUM(ISNULL(Upvotes, 0)) 
     FROM dbo.Posts WHERE UserID = @UserID

   SELECT @EventPoints = SUM(ISNULL(Upvotes, 0))
     FROM dbo.Events WHERE UserID = @UserID

   SELECT @CommentPoints = SUM(ISNULL(Upvotes, 0)) - SUM(ISNULL(Downvotes, 0))
     FROM dbo.Comments WHERE UserID = @UserID

   -- updated: using RETURN gives you a method on your Linq context that you can
   -- easily call like this:
   --
   -- int myUserPoints = dataContext.GetUserPoints(......)
   --
   RETURN @PostPoints + @EventPoints + (@CommentPoints / 5)
END

Linq-to-SQL DataContext, proc , :

public int GetUserPoints(string userName)
{
    return db.GetUserPoints(userName);
}

Answer 3

, /, , L2S . . / (, ..). (, 1:1 ).