0

Here is the scenario -

We have an application that supports posting to multiple social sites (Facebook, Twitter, G+, etc).

We used TPH to structure this, all in one Posts table, with a discriminator column. Each of these "Social" posts derives from a common base class called, appropriately, SocialPost.

It was that we would just say context.SocialPosts and it would grab everything with a discriminator for FacebookPost, TwitterPost, and GooglePost.

Now we have a need to be able to grab a mix-and-match of the types. Based on a setting, we want to pull back all, for example, Facebook and Google Posts to process, while leaving Twitter untouched.

Is there a sane way to do that in one query?

The following code

context.SocialPosts.Where(x=>x is FacebookPost || x is GooglePost)

will produce SQL like the following

SELECT * FROM Posts WHERE Discriminator IN ('FacebookPost', 'TwitterPost', 'GooglePost') AND Discriminator IN ('FacebookPost','GooglePost')

which is clearly not what I want. I'm not sure if having the double filter on the Discriminator matters, per se, but it's definitely weird SQL to be using.

So, is there a sane way to grab multiple discriminator types with one query, or would it be better to grab all SocialPosts and then filter out the ones I don't want on the application end.

Thomas Jones
  • 4,892
  • 26
  • 34
  • What does your execution plan actually do? The double filter on the Discriminator probably doesn't matter. And have you tried `context.Posts.Where(x=>x is FacebookPost || x is GooglePost)`? – Colin Feb 05 '15 at 09:45
  • I haven't looked at the execution plan, though that is a place to start. context.Posts.Where will produce a larger "Discriminator IN" up front, as we have more derived types off of Post that aren't Social Posts. – Thomas Jones Feb 05 '15 at 11:31
  • Did you try it? I can see why the EF team would have to add all the discriminators for sub-classes when you call `context.SocialPosts`. But adding all the discriminators to `context.Posts` is unnecessary because it is the root of them all – Colin Feb 05 '15 at 15:20
  • Just got a chance to get back to this. Haven't looked at the execution plan yet, but `context.Posts.ToArray()` yields `WHERE [Extent1].[Discriminator] IN (N'NewsletterPost',N'SocialPost',N'FacebookPost',N'TwitterPost',N'GooglePost',N'Post')`. Now, this doesn't mean that we don't have something horribly misconfigured somewhere. – Thomas Jones Feb 06 '15 at 15:46
  • apparently SQL Server _will_ optimize out the first `WHERE` clause, and look at just the other one. My execution plan is a single ClusteredIndexScan, which we will need to fix for other perf reasons. But I guess that doing it by the expression tree of combined `is` calls is sufficient. Now the question would be how to build that dynamically based off a flags-enum value set, but that's an issue for another day. – Thomas Jones Feb 06 '15 at 18:28

0 Answers0