Linq Version of SQL "In" Statement

Linq version of SQL IN statement

Something like

var TagIds = new int[] {12, 32, 42};

var q = from map in Context.TagMaps
where TagIds.Contains(map.TagId)
select map.Items;

should do what you need. This will generate an In ( 12, 32, 42 ) clause (or more specifically a parameterized IN clause if I'm not mistaken).

What is the equivalent of this SQL statement in Linq?

You can use the aliases with the new object initializer with the code below, which will also support multiplying the tarif:

select new {
f.ID,
Tarif = ftf.Tarif * 1, // Alias and multiply by your number
f.Time,
f.TimeOfArrival,
SourceName = sl.Name, // Alias
SourceCountry = sl.Country, // Alias
SourceAirport = sl.Airport, // Alias
DestName = dl.Name, // Alias
DestCountry = dl.Country, // Alias
DestAirport = dl.Airport // Alias
};

Just to provide a few more details in case others stumble on this, the root cause is that the code was using the new keyword to define an anonymous type with an object initializer that ran into multiple conflicts trying to define the anonymous class (multiple properties with same inferred name, and then unable to name property from expression when tarif was multiplied).

By explicitly naming the properties with conflicts, the compiler no longer had to infer the naming that generated the conflicts.

More: http://geekswithblogs.net/BlackRabbitCoder/archive/2012/06/21/c.net-little-wonders-the-joy-of-anonymous-types.aspx

The link above has some additional examples on how to use the object initializer with anonymous types.

Linq to Entities - SQL IN clause

You need to turn it on its head in terms of the way you're thinking about it. Instead of doing "in" to find the current item's user rights in a predefined set of applicable user rights, you're asking a predefined set of user rights if it contains the current item's applicable value. This is exactly the same way you would find an item in a regular list in .NET.

There are two ways of doing this using LINQ, one uses query syntax and the other uses method syntax. Essentially, they are the same and could be used interchangeably depending on your preference:

Query Syntax:

var selected = from u in users
where new[] { "Admin", "User", "Limited" }.Contains(u.User_Rights)
select u

foreach(user u in selected)
{
//Do your stuff on each selected user;
}

Method Syntax:

var selected = users.Where(u => new[] { "Admin", "User", "Limited" }.Contains(u.User_Rights));

foreach(user u in selected)
{
//Do stuff on each selected user;
}

My personal preference in this instance might be method syntax because instead of assigning the variable, I could do the foreach over an anonymous call like this:

foreach(User u in users.Where(u => new [] { "Admin", "User", "Limited" }.Contains(u.User_Rights)))
{
//Do stuff on each selected user;
}

Syntactically this looks more complex, and you have to understand the concept of lambda expressions or delegates to really figure out what's going on, but as you can see, this condenses the code a fair amount.

It all comes down to your coding style and preference - all three of my examples do the same thing slightly differently.

An alternative way doesn't even use LINQ, you can use the same method syntax replacing "where" with "FindAll" and get the same result, which will also work in .NET 2.0:

foreach(User u in users.FindAll(u => new [] { "Admin", "User", "Limited" }.Contains(u.User_Rights)))
{
//Do stuff on each selected user;
}

LINQ equivalent of SQL NOT IN statement

You can use Contains with !. In addition, if you just want to count rows, you can use Count.

var ids = new List<int> {25, 346, 350, 352, 353, 354, 355, 357, 358, 366, 372, 411};

var querynonSystem = XXXcontext.Rpm_scrty_rpm_usrs.Count(x =>
x.Inact_ind == "N" &&
x.Email_id != null &&
x.Wwid == null &&
x.Email_id.Trim() != "" &&
!ids.Contains(x.Dflt_ste_id));

From comment: if you want to retrieve all, you can still use Where and Select.

var querynonSystem = XXXcontext.Rpm_scrty_rpm_usrs.Where(x => 
x.Inact_ind == "N" &&
x.Email_id != null &&
x.Wwid == null &&
x.Email_id.Trim() != "" &&
!ids.Contains(x.Dflt_ste_id)).Select(x => x).ToList();

FYI: you cannot call Rpm_scrty_rpm_usrs table class to query. Instead, you need DbContext or some other repository.

What is the linq equivalent to the SQL IN operator

.Contains

var resultset = from x in collection where new[] {2,3,4,5}.Contains(x) select x

Of course, with your simple problem, you could have something like:

var resultset = from x in collection where x >= 2 && x <= 5 select x

LINQ/Lambda equivalent of SQL in

If you are filtering you need to do that in the where clause not the select clause

var movieratings = new int[] {1, 2, 7, 8, 9, 10, 11};
list.ratings = list.ratings.Where(x => movieratings.Contains(x.Value));


Related Topics



Leave a reply



Submit