How to write LINQ query to sql IN

linq select query with where clause in c#
how to write linq query in c#
linq to sql
linq query join
linq tutorial
linq query syntax vs method syntax
linq query syntax join
where in linq query with multiple conditions

I want to query my cosmosDB to get List of Documents of type ZonesDO && whose id are in UserPreferance.Zones My UserPreferance class is :

public class UserPreference 
    {
        [JsonProperty("zones")]
        public List<Zone> Zones { get; set; }
    }

and Zone Class is:

public class Zone
    {
        [JsonProperty("id")]
        public override Guid Id { get; set; }

        [JsonProperty("name")]
        public string Name { get; set; }

        [JsonProperty("category")]
        public string Category { get; set; }
    }

I am trying this query but not able to complete it.

var zones = DbUtil.Client.CreateDocumentQuery<ZoneDO>(CollectionUri)
     .Where(z => z.Type == typeof(ZoneDO).ToString() && 
     *z.Id in user.UserPreference.Zones.ids*)// here I need the solution
     .AsEnumerable().ToList();

Perform select on Zones.Id and then check with Contains to get desired result

var zones = DbUtil.Client.CreateDocumentQuery<ZoneDO>(CollectionUri)
     .Where(z => z.Type == typeof(ZoneDO).ToString() && 
    user.UserPreference.Zones.Select(x => x.Id).Contains(z.Id))
     .AsEnumerable().ToList();

Various Ways To Write LINQ Queries, Walkthrough: Writing Queries in C#. Note. If you already are familiar with a query language such as SQL or XQuery, you can skip most of this  Learn How to Use SQL Quickly and Effectively And Become a Data Analysis Expert in Days! Join Over 50 Million People Learning Online with Udemy. 30-Day Money-Back Guarantee!

You can try to use Contain method.

 var zones = DbUtil.Client.CreateDocumentQuery<ZoneDO>(CollectionUri)
     .Where(z => z.Type == typeof(ZoneDO).ToString() && 
      user.UserPreference.Zones.Select(x => x.Id).Contain(z.Id)).ToList();

Or you can use inline Where and Count

var zones = DbUtil.Client.CreateDocumentQuery<ZoneDO>(CollectionUri)
     .Where(
        z => z.Type == typeof(ZoneDO).ToString() && 
        user.UserPreference.Zones.Where(a=> a.Id == z.Id).Count() > 0
      ).ToList();

What is the Difference Between LINQ and SQL, The following is a sample LINQ query that returns a collection of strings which contains a word "Tutorials". Example: LINQ Query Syntax in C#. // string collection​  The first parameter is the SQL query. The second parameter represents that you can include parameter placeholders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter and it's an optional parameter.

Another option may be to use the Any() operator:

var zones = DbUtil.Client.CreateDocumentQuery<ZoneDO>(CollectionUri)
            .Where(z => z.Type == typeof(ZoneDO).ToString() && 
            user.UserPreference.Zones.Where(x => x.Id == z.Id).Any())
            .AsEnumerable().ToList();

Part 1 LINQ to SQL, You have two options: you can do the casting and sorting on client: db.​BookingArriveds .Where(item => item.BookingArrivedEnquiredDateTime  I'm a bit stuck on this. Basically I want to do something like the following SQL query in LINQ to SQL: SELECT f.* FROM Foo f WHERE f.FooId IN ( SELECT fb.FooId FROM FooBar fb WHERE fb.BarId = 1000 ) Any help would be gratefully received. Thanks.

Linq providers usually understands basic methods on arrays/collections. Therefore you can use Contains method.

var zones = DbUtil.Client.CreateDocumentQuery<ZoneDO>(CollectionUri)
     .Where(z => z.Type == typeof(ZoneDO).ToString() && 
     user.UserPreference.Zones.Contains(z.Id))
     .AsEnumerable().ToList();

What is LINQ, LINQ to SQL is an ORM (Object Relational Mapping) framework that can automatically create the stored type . NET classes based on the database tables. We can, then, write LINQ to SQL queries like Select, Insert, Update, and Delete, in any . The query given above is what we had, which is going to retrieve a single student whose ID is 1. When this LINQ query is issued, LINQ must pass this expression tree and dynamically generate the required T-SQL statements, which will be executed under the underlying database. This parsing happens every time when a LINQ query is issued.

So apparently you have a user. This user has a UserPreference and this UserPreference has zero or more Zones. It seems that this user, and the Zones of this user's UserPreference is in local memory (not in a database. The Zones are IEnumerable and not IQueryable)

Although you didn't specify, it seems that DbUtil.Client.CreateDocumentQuery<ZoneDO>(CollectionUri) returns an IQueryable<ZoneDO>

You want all ZoneDo that have an Id that is an Id of one of the Zones in the one and only UserPreference of your user.

In baby steps:

IEnumerable<Guid> zoneIds = user.Userpreference.Zones.Select(zone => zone.Id);
IQueryable<ZoneDO> allZoneDOs = DbUtil.Client.CreateDocumentQuery<ZoneDO>(CollectionUri);

IEnumerable<ZoneDO> requestedZoneDOs = allZoneDOs
    .Where(zoneDo => zoneIds.Contains(zoneDo.Id);

Until now no query is performed, nor any enumeration. To perform the query and return the fetched data as a List<ZoneDO> use .ToList();

List<ZoneDO> documents = requestedZoneDOs.ToList();

TODO: if desired put all statements into one big LINQ statement. I doubt whether this will improve performance. It surely will deteriorate readability and thus maintainability.

Did you notice that I did not do your type checking part. This was not needed, because function CreateDocumentQuery<ZoneDO> already returns a sequence f ZoneDo.

If not, and there are other types in the returned sequence, use OfType<ZoneDo> instead of checking on string representation of the returned objects:

IQueryable<ZoneDO> allZoneDOs = DbUtil.Client
    .CreateDocumentQuery<ZoneDO>(CollectionUri)
    .OfType<ZoneDo>();

AsEnumerable is not needed. IQueryable.ToList() will perform the query and convert the data to a list.

AsEnumerable is only needed if you need the data in local memory to continue with LINQ statements that cannot be performed as IQueryable, like LINQ statements where you call local functions, or LINQ statements that cannot be translated into SQL.

AsEnumerable will fetch the requested data per 'page'. So if you only need the first (few) elements, you don't fetch the complete table, but only the first (few) pages.

Basic LINQ Query Operations (C#), To compile a LINQ query, we use compiled query class that is present in system.​data.linq namespace. Let's look at an example. First, let's create  In this article, we will study LINQ in detail and will learn about its different features, we will also learn different methods of how we can convert MySQL query in LINQ query. Different phases of LINQ will also be discussed in this assignment including different operations of LINQ, different technologies that provide working with LINQ, etc

LINQ Query Syntax, Check SQL query submitted to database. Pull only the needed columns. When working with LINQ, only  This article shows the three ways in which you can write a LINQ query in C#: Use query syntax. Use method syntax. Use a combination of query syntax and method syntax.

How to write linq query from sql query using LINQ?, Using LINQ to SQL, you can write a simple query such as the following: var query = from c in Customers where c.Country == "USA" && c. There are Different Types of SQL Joins which are used to query data from more than one database tables. In this article, you will learn about how to write SQL joins queries in LINQ using C#. LINQ has a JOIN query operator that gives you SQL JOIN like behavior and syntax. Types of LINQ Joins. Venn diagram for LINQ Joins.

How To Use LINQ To SQL For Retrieving Data From SQL Server , For another simple example of using our data model's association relationships within queries, we could write the below LINQ query to retrieve  In cases where a LINQ to SQL query is insufficient for a specialized task, you can use the ExecuteQuery method to execute a SQL query, and then convert the result of your query directly into objects. In the following example, assume that the data for the Customer class is spread over two tables (customer1 and customer2). The query returns a

Comments
  • You don't need AsEnumerable(), ToList() will execute the query and return a list with all results.