SQL IN Operator to LINQ

Question

I need this query to be translated to Linq query

SELECT DISTINCT (pf.Id)
FROM
    PF pf
    LEFT JOIN FA fa on pf.id = fa.PFId
    LEFT JOIN Fan f ON pf.FId = f.Id
WHERE
  pf.PId=2 AND fa.AId IN (1,26) AND fa.AId NOT IN(27)

This is the LINQ query I have so far as requested

var allFansSavedAsLeads =  _dbContext.PF
                           .Where(e => e.F.S != null &&
                           e.A.Any(x => x.AId==27 && 
                           x.AId.Equals(1) /*&& 
                           x.AId != 27*/) && 
                           e.PId == pId);

I get zero results with this.

Can you please update the post with your LINQ query that you are having — sujith karivelil, Jul 30 '19 at 10:39
Possible duplicate of [SQL to LINQ Tool](https://stackoverflow.com/questions/296972/sql-to-linq-tool) — GoldenAge, Jul 30 '19 at 10:40
x.ActivityId cannot both equal 27 and 1. You need an OR (||) not an AND (&&) there. — Mark Cilia Vincenti, Jul 30 '19 at 10:47

sujith karivelil · Answer 1 · 2019-07-30T10:53:56.413

1

I suggest you Create two lists of Ids representing the Activities that can be included and activities which needs to be excluded. use them like the following:

List<int> IncludedIds = new List<int>(){1,26};
List<int> ExcludedIds = new List<int>(){27};
_dbContext.ProfileFans.Where(e => e.Fan.SNUrl != null && 
                             e.Activities.Any(x => IncludedIds.Any(x.ActivityId) && 
                                                   !ExcludedIds.Any(x.ActivityId) && 
                                                   e.ProfileId == profileId);

Please note: I used List<int> because of the example that you are given, you have to create the lists based on the data type of ActivityId

edited Jul 30 '19 at 10:53

answered Jul 30 '19 at 10:48

sujith karivelil

28,671
6
55
88

The exclude/black list is completely useless. – Mark Cilia Vincenti Jul 30 '19 at 12:07
@MarkCiliaVincenti: yes until and unless there is only one item in that list. – sujith karivelil Jul 30 '19 at 12:15
Even if there are more items in that list. If you're taking only IDs 1 and 26, then by definition the ID can't be 27, and it can't be 28, and it can't be 29... Check my answer at https://stackoverflow.com/a/57270025/9945524 to understand what I mean. – Mark Cilia Vincenti Jul 30 '19 at 12:16

score 0 · Answer 2 · answered Jul 30 '19 at 10:48

0

You can create a temporary ActivityList AS

var List<int> ActivityList = new List<int>() {1, 26}

and use something like

ActivityList.Contains(x => x.ActivityId)

But see sujith's answer for a more complete solution.

answered Jul 30 '19 at 10:48

Peter Smith

5,528
8
51
77

Mark Cilia Vincenti · Answer 3 · 2019-07-30T12:53:44.073

You don't need a whitelist and a blacklist. It's either one or the other. So I'm making a whitelist. If the allowed ActivityId is 1 or 26, then by definition it is definitely not 27, so there is no need to try and exclude it. I'm using int[] instead of List<int> given that the whitelist is likely to be static, but feel free to change this to a List<int> if you want to dynamically modify it.

int[] whiteList = { 1, 26 };
var allFansSavedAsLeads = _dbContext.ProfileFans.Where(pf =>
    pf.Fan.SNUrl.HasValue &&
    pf.Activities.Any(fa => whiteList.Contains(fa.ActivityId)));

If you want the JOINs as well, you may want to look into .Include(), but from your original SQL query you seem like you're not going to actually need the contents of the joined tables.

SQL IN Operator to LINQ

3 Answers3