Azure WebJobs and models and SQL queries

Question

I've got an Azure web app serving as a restful API. This back end defines the models being used by my system: Users, Teams, Projects, Items. A User can join many Teams, and the Team-->Project-->Item structure is a Grandparent-->Parent-->Child relationship.

I want to set up a daily email digest of updates for each User. I want this to be run in an Azure web job in the background, so as not to burden the customer facing API server.

When writing code in the API's controllers, I can write easy queries like this to get all the items that belong to a certain team (because the models and their hierarchical relationships are defined in the API):

var items = await (from x in db.Items
                  where x.Project.Team.TeamId == teamId
                  select x).ToListAsync();

However, in the web job, I can't do that - there's no models to speak of. I see a couple options:

Option A) Somehow use (or re-create) the models (and the DB context?) in the web job. If this is possible (and not a terrible idea), I think this is what I want.

Option B) Make peace with many, verbose SQL queries in the web job. I can query the User table for what Teams they're part of. Then, for each Team, I can query the Project table for a set of Projects. Then, for each Project, I can query the Items table for the Items in each project.

Is there a way to make a magical SQL query that works more efficiently than Option B's many calls to the database? I've tried things like the following, but haven't found a working scheme:

SELECT * FROM Items WHERE Projects WHERE Teams = teamId;

@jarlh good point - edited. That surely wasn't the only thing wrong with my made up query :) — waffles, Jun 05 '16 at 21:25

score 1 · Answer 1 · answered Jun 07 '16 at 07:41

1

The best way from my point of view is Opition C) - refactor your solution and create separate project that will contain data context, models and all migrations. After that you can reference this project from your main app and from WebJob. You can also move some core database-related functions into this project to make them accessible from other projects.

answered Jun 07 '16 at 07:41

Pavel Kutakov

971
1
7
11

That sounds like a pretty reasonable thing to do - too much to change right this moment, but maybe soon! – waffles Jun 08 '16 at 03:08

score 0 · Accepted Answer · edited May 23 '17 at 10:29

Well, it appears what I wanted was a JOIN: What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

I've really only ever used LINQ to pull from SQL databases, now I know how to do it the old fashioned, more verbose, less intuitive way :)

Here's the code that I'm using to pull all the items that belong to a given team:

SqlCommand command = new SqlCommand();
command.CommandText = @"SELECT Items.* FROM Items 
                        JOIN Projects
                            ON Projects.ProjectId = Items.ProjectId
                        JOIN Teams
                            ON Teams.TeamId = Projects.TeamId
                    WHERE Teams.TeamId = '" + teamId.ToString() + "'";

Azure WebJobs and models and SQL queries

2 Answers2