linq to sql, make one db call instead of three

Question

Hi im calculating a users points based on upvotes they have recieved for posts they have submitted, events they have submitted and comments that have recieved points -

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?

Just a tip: use postPoints ?? 0 instead of (postPoints.HasValue ? postPoints.Value : 0) — Dan Abramov
– Dan Abramov, Commented Feb 5, 2011 at 11:54

Amy West · Accepted Answer · 2011-02-05 14:45:35Z

5

If you really need just 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()

answered Feb 5, 2011 at 14:45

Amy West

1638 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Saunders Over a year ago

does that translate to a single SQL query? Could you post the query it translates to?

marc_s · Accepted Answer · 2011-02-05 16:53:33Z

2

For something like this, it would be a good idea to create a stored procedure on your SQL Server that does all that stuff for you (join the tables, select the user's posts and so forth) and then returns just the values you need.

You can easily call a stored proc from Linq-to-SQL and get back the results.

You didn't show the exact table structure, but it probably would be something like:

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

and then add this stored procedure to your Linq-to-SQL DataContext and you should be able to call that stored proc as a method on your data context, something like this:

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

edited Feb 5, 2011 at 16:53

answered Feb 5, 2011 at 13:48

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

2 Comments

raklos Over a year ago

I had to do this - return (int)db.GetUserPoints(userName).SingleOrDefault().Column1; is there a nicer way? other than that it works perfect

marc_s Over a year ago

@raklos: if you change the last line in the stored procedure to RETURN @PostPoints + @EventPoints + (@CommentPoints / 5) then you should be able to call the stored proc and just get back a return value as an int

Randy Minder · Accepted Answer · 2011-02-05 11:23:52Z

1

One easy way would be to create a view in your database that does all the queries/calculations you need and then use L2S to query the view. This would result in a single call to your database. We do this on occasion to save on calls to the database, and/or if our query has special needs (like lock hints etc...). Using views is also a great way to hydrate special domain objects (objects that don't have a 1:1 relationship to a table in the database).

answered Feb 5, 2011 at 11:23

Randy Minder

48.8k56 gold badges222 silver badges373 bronze badges

3 Comments

raklos Over a year ago

my sql is really bad, can i create one sql query that can do all this?

Randy Minder Over a year ago

I really don't know if this can be accomplished via a single T-SQL statement without more information about your database schema. But, IMO, that's not really the issue here. What I might do here is to capture the T-SQL statement(s) being generated by your L2S statements. You can do this with a tool like LinqPad. Then you can take the generated T-SQL and create a View around it.

Esben Skov Pedersen Over a year ago

To see the sql just set the Log property on your datacontext to Response outputstream

Collectives™ on Stack Overflow

linq to sql, make one db call instead of three

3 Answers 3

1 Comment

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related