0

I have the below SQL query, Which I need to convert to LINQ. The SQL query:

select distinct a.DeliverableAdditionalFieldId, b.FieldName, b.CustomFieldType
from PrefixDeliverableAdditionalFieldsMapping as a, DeliverableAdditionalField as b
where a.DeliverableAdditionalFieldId = b.Id
and b.IsActive = 1
and a.PrefixId in (184,183)

I converted the sql query to LINQ which is below:

var prefixIds = "184, 183"

from prefix in context.PrefixDeliverableAdditionalFieldsMappings
join additionalField in context.DeliverableAdditionalFields on prefix.DeliverableAdditionalFieldId equals additionalField.Id
where additionalField.IsActive == true && prefixIds.Contains(prefix.PrefixId.ToString())
select new AdditionalCustomField
{
    AdditionalFieldId = prefix.DeliverableAdditionalFieldId,
    AdditionalFieldName = additionalField.FieldName,
    FieldType = additionalField.CustomFieldType

}).Distinct().ToList()

For IN Operator of SQL, in LINQ I used the code : prefixIds.Contains(prefix.PrefixId.ToString(). Now I need to sure that this is OK. Please let me know is there any other alternative way?

And is there any alternate way to use distinct and the way I used, will it work?

mnu-nasir
  • 1,642
  • 5
  • 30
  • 62
  • I am weak in LINQ. I just to make sure there is no problem in the LINQ query and it will work fine. – mnu-nasir Mar 08 '21 at 13:56
  • `prefixIds` is a string, not a collection, so `prefixIds.Contains(prefix.PrefixId.ToString())` (assuming it doesn't throw an exception) will be translated to `LIKE` – Panagiotis Kanavos Mar 08 '21 at 13:58
  • @mnu-nasir for starters, LINQ isn't a replacement for SQL or a way to write embedded SQL. It's a query language meant to work on top of an ORM. It's the ORM's job to generate the JOINs from the relations between entities. Using LINQ to approximate SQL is harder than writing and executing a SQL query – Panagiotis Kanavos Mar 08 '21 at 14:00
  • Looks like you're weak in SQL also. [Don't use `,` cross join syntax](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins), it's been deprecated for many years, use proper `join DeliverableAdditionalField daf on daf....`. [Don't use meaningless aliases like `a` and `b`](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3). [Don't just stuff in a `DISTINCT` to filter out duplicates](https://stackoverflow.com/questions/5341276/sql-distinct-keyword-bogs-down-performance), think about whether to use nested `GROUP BY` or `EXISTS` – Charlieface Mar 08 '21 at 14:26
  • Does this answer your question? [Linq version of SQL "IN" statement](https://stackoverflow.com/questions/896123/linq-version-of-sql-in-statement) – Sinatr Mar 08 '21 at 14:26

1 Answers1

4

You are now doing .Contains() on a string, so e.g. "1" will also be found, and I'm pretty sure you don't want that to happen.

Instead, define prefixIds as a collection of the desired type, e.g. int[]:

var prefixIds = new[] { 184, 183 };

And then you can use it as follows:

where ..... && prefixIds.Contains(prefix.PrefixId)

This translates to a SQL IN (...) clause.

Peter B
  • 22,460
  • 5
  • 32
  • 69
  • if you apply Contains on string, use toUpper/Lower or .Contains(myStringToCheck, StringComparison.OrdinalIgnoreCase) – Power Mouse Mar 08 '21 at 14:11