LINQ to SQL: Advanced Queries for Lists, Arrays, and Object Lists

Question

Single and multiple lists

Consider the following lists:

List<Int32> appleIdentities = new List<int>(new[] { 1, 2, 3 });
List<Int32> chocolateIdentities = new List<int>(new[] { 2, 3, 4 });
List<Int32> icecreamIdentities = new List<int>(new[] { 11, 14, 15, 16 });

Using LINQ to SQL; Is it possible to write a statement that translates into:

SELECT
    DesertsID,
    DesertsName
FROM
    Deserts
WHERE
    Deserts.AppleIdentity IN (1, 2, 3) AND
    Deserts.ChocolateIdentity IN (2, 3, 4) AND
    Deserts.IcecreamIdentity IN (11, 14, 15m 16)

If yes; what would the code look like if I wanted to query my desert database only in the list appleIdentities?







Arrays

Consider the following arrays:

Int32[] appleIdentities = new[] {1, 2, 3, 4};
String[] chocolateNames = new[] {"Light", "Dark"};

Using LINQ to SQL; Is it possible to write a statement that translates into:

SELECT
    DesertsID,
    DesertsName
FROM
    Deserts
WHERE
    Deserts.AppleIdentity IN (1, 2, 3) AND
    Deserts.ChocolateName IN ('Light', 'Dark')

If yes; what would the code look like if I wanted to query my desert database for an array only appleIdentities?







List of objects

Consider the following:

public class Identities
{
    public Int32 appleIdentity { get; set; }
    public String chokolateName { get; set; }
}

List<Identities> identities = new List<Identities>(new[] {
    new Identities { appleIdentity = 1, chokolateName = "Light" },
    new Identities { appleIdentity = 2, chokolateName = "Dark" },
});

Using LINQ to SQL; Is it possible to write a statement that translates into:

SELECT
    DesertsID,
    DesertsName
FROM
    Deserts
WHERE
    Deserts.AppleIdentity IN (1, 2) AND
    Deserts.ChocolateName IN ('Light', 'Dark')

If yes; what would the code look like if I wanted to query my desert database with only appleIdentity-property in my list of objects Identities?





This is a branch of the LINQ to SQL query to the entity list.

Answer 1

- Contains - Northwind :

var qry = from cust in ctx.Customers
          where custIds.Contains(cust.CustomerID)
             && regions.Contains(cust.Region)
          select cust; // or your custom projection

Answer 2

, appleIdentities?

linq , , , where.

var query = db.Desserts;
if (filterbyAppleIdentity)
    query = query.Where( q => appleIdentities.Contains(q.DesertsID));
if (filterbyChocolateIdentities)
    query = query.Where( q => chocolateIdentities.Contains(q.DesertsID));
if (filterbicecreamIdentities)
    query = query.Where( q => icecreamIdentities.Contains(q.DesertsID));

var deserts = query.ToList();

: ( , IQueriable

public static class LinqExtensions {
  public IQueriable<T> CondWhere<T>(this IQueriable<T> query, bool condition, Expression<Func<T,bool>> predicate) {
     if (condition)
        return query.Where(predicate);
     else 
        return query;
  }
 }

linq :

  var deserts = db.Desserts;
      .CondWhere(filterbyAppleIdentity, q => appleIdentities.Contains(q.DesertsID));
      .CondWhere(filterbyChocolateIdentities, q => chocolateIdentities.Contains(q.DesertsID));
      .CondWhere(filterbicecreamIdentities, q => icecreamIdentities.Contains(q.DesertsID)).ToList();

: :

var deserts = db.Deserts
        .Where( d => appleIdentities.Union(chocolateIdentities).Union(icecreamIdentities).Contains(d.DesertsID);

, . , int IEnumerable, :

var deserts = db.Deserts
    .Where(d => 
        identities.Select(i => i.appleIdentity).Contains(d => d.DesertID) &&
        identities.Select(i => i.chokolateName).Contains(d => d.DesertsName)
     )

Answer 3

, :

var query = from dessert in db.Desserts
            where appleIdentities.Contains(dessert.AppleIdentity)
               && chocolateIdentities.Contains(dessert.ChocolateIdentity)
               && iceCreamIdentities.Contains(dessert.IceCreamIdentity)
            select new { dessert.Id, dessert.Name };

, , , IIRC. .

- , .

Answer 4

, LinqToSql Contains IN.

:

• List<T>.Contains(), IList<T>.Contains(). ? .
• - sql. SQL Server 2008 2000 sql- , .
• This translation, applied to the rowset, will create the nvarchar parameters . This can be a serious problem if the target column is varchar and you want to use the index in that column. Sql Server converts the index, not the parameters ... which includes reading and converting each row in the entire index.

Here is the code for your question List of objects :

List<int> someIDs = identities
   .Select(x => x.appleIdentity).ToList();
List<string> someStrings = identities
   .Select(x => x.chokolateName).ToList();

var query = db.Desserts.Where(d =>
  someIDs.Contains(d.AppleIdentity) &&
  someStrings.Contains(d.ChocolateName)
  )