How to Handle an in Sub-Query with Linq to SQL

How can you handle an IN sub-query with LINQ to SQL?

Have a look at this article. Basically, if you want to get the equivalent of IN, you need to construct an inner query first, and then use the Contains() method. Here's my attempt at translating:

var innerQuery = from fb in FoorBar where fb.BarId = 1000 select fb.FooId;
var result = from f in Foo where innerQuery.Contains(f.FooId) select f;

LINQ to SQL, using subquery with IN operator

Looking at your code I think this is what you need. By doing it this way you can also avoid the unions.

var friends_A = from f in entities.Friends
                   where f.Friend_A != User.Identity.Name
                   && f.Friend_B == User.Identity.Name
                   select f.Friend_A;

var friends_B = from f in entities.Friends
                   where f.Friend_A == User.Identity.Name
                   && f.Friend_B != User.Identity.Name
                   select f.Friend_B;

var posts = 
     from p in entities.Posts
     let userName = p.UserName.ToLower()
     where 
        userName == User.Identity.Name ||
        friends_A.Concat(friends_B).Contains(userName)
     orderby 
        p.DateAndTime
     select new 
     { 
        p.UserName, 
        p.DateAndTime, 
        p.PostText 
     };

LINQ TO SQL Subquery

This should work for you:-

var result = (from x in
               ((from data in db.DATA orderby data.ID descending select data).Take(2))
                orderby x.ID
                select x).FirstOrDefault();

How to do a subquery in LINQ?

Here's a subquery for you!

List<int> IdsToFind = new List<int>() {2, 3, 4};

db.Users
.Where(u => SqlMethods.Like(u.LastName, "%fra%"))
.Where(u =>
    db.CompanyRolesToUsers
    .Where(crtu => IdsToFind.Contains(crtu.CompanyRoleId))
    .Select(crtu =>  crtu.UserId)
    .Contains(u.Id)
)

---

Regarding this portion of the question:

predicateAnd = predicateAnd.And(c => c.LastName.Contains(
                                TextBoxLastName.Text.Trim()));

I strongly recommend extracting the string from the textbox before authoring the query.

string searchString = TextBoxLastName.Text.Trim();
predicateAnd = predicateAnd.And(c => c.LastName.Contains( searchString));

You want to maintain good control over what gets sent to the database. In the original code, one possible reading is that an untrimmed string gets sent into the database for trimming - which is not good work for the database to be doing.

Sql Query with not in clause of subquery to LINQ query

This

    from a in user_user_connection 
    join b in user_user_connection on 
        new {From=a.userid_to, To=a.userid_from} equals new {From=b.userid_from, To=b.userid_to} into c
    from d in c.DefaultIfEmpty()
    where d == null
    select a;

is similar to

select a.*
from user_user_connection a
left join user_user_connection b on a.userid_to = b.userid_from and a.userid_from = b.userid_to
where b.userid_from is null

which should match your not in query.

if you want a specific userid_from you can add another where

    from a in user_user_connection 
    where a.userid_from == 3464
    join b in user_user_connection on 
        new {From=a.userid_to, To=a.userid_from} equals new {From=b.userid_from, To=b.userid_to} into c
    from d in c.DefaultIfEmpty()
    where d == null
    select a;

LINQ how to translate subquery in FROM clause

What may help is going through a couple sample linq queries and look at the results. For example, the following code will create an IEnumerable of val1's.

var subSelect1 = (from val1 in aTable 
    where  val1.foo = "bar" 
);

Note the above is equivalent to the below.

var subSelect1 = (from val1 in aTable 
    where  val1.foo = "bar" 
    select val1 /* this select statement is implied in the above */
);

Adding the select new {val1.foobar} after the where clause creates an IEnumerable of an anonymous type, with one property named foobar. This means that you'll only be able to join against the one property foobar.

var subSelect1 = (from val1 in aTable
         where  val1.foo == "bar"
         select new {val1.foobar}
    );

var mainSelect = (from f in subSelect1
         where f.foobar == "test")

By leaving out the select new, you'll have access to all the fields in val1.

var subSelect1 = (from val1 in aTable 
    where  val1.foo == "bar" 
);

var mainSelect = (from v in subSelect1
    where v.foobar == "test"
        and v.bar == "status"
)

linq inner join sub query and conditional select

you can use Lambda expressions group by and for conditional select use inline condition like below:

var query = (from u in UsageLogs
            join c in (UsageLogs.GroupBy(r => r.username).Select(r => new {username = r.Key, lastStart = r.Max(p => p.start)))
            on u.username equals c.username
            orderby u.lastname,u.firstname,u.program.u.start
            select new
            {
                lastname = u.lastname,
                firstname = u.firstname,
                program = u.program,
                start = u.start,
                end = u.end,
                error = (u.end == NULL && u.start < c.lastStart) ? 1 : 0,
                loggedOn = (u.end == NULL && u.start == c.lastStart) ? 1 : 0,
            });

LINQ query with sub-query on LEFT JOIN conditions

after some research I finally found how to do it. Here is the LINQ query that generates the SQL I was trying to get:

var leaseList = (from l in leases.tblfLeaseDetails
                             join p in leases.tblfPayments
                             on l.Lease_Detail_ID equals p.Lease_Detail_ID into lp
                             from jlp in lp.Where(x => x.Payment_ID == (from pj in leases.tblfPayments
                                                                          where pj.Lease_Detail_ID == l.Lease_Detail_ID
                                                                          orderby pj.Payment_Date ascending
                                                                          select pj.Payment_ID).FirstOrDefault()).DefaultIfEmpty()
                             join a in leases.tblfAuthorizations on l.Lease_Detail_ID equals a.Lease_Detail_ID into la
                             from jla in la.Where(x => x.Authorization_ID == (from aj in leases.tblfAuthorizations
                                                                                  where aj.Lease_Detail_ID == l.Lease_Detail_ID
                                                                                  orderby aj.Authorized_Date ascending
                                                                                  select aj.Authorization_ID).FirstOrDefault()).DefaultIfEmpty()                            
                             join v in leases.tblvVendors on l.Vendor_ID equals v.Vendor_ID into lv
                             from jlv in lv.DefaultIfEmpty()
                             join c in leases.tblvCounties on l.County_ID equals c.County_ID into lc
                             from jlc in lc.DefaultIfEmpty()
                             select new LeaseViewModel()
                             {
                                 Lease_Detail_ID = l.Lease_Detail_ID,
                                 Lease_ID = l.Lease_ID,
                                 XRef_Lease_ID = l.XRef_Lease_ID,
                                 Vendor_Name = jlv.Vendor_Name,
                                 Description = l.Description,
                                 County = jlc.County,
                                 Amount = l.Amount,
                                 Payment_Due_Date = l.Payment_Due_Date,
                                 Lease_Type = l.Lease_Type.ToString(),
                                 Location_ID = l.Location_ID,
                                 Active = l.Active,
                                 Expiration_Date = l.Expiration_Date,
                                 Authorized = jla.Authorized,
                                 Payment_Date = jlp.Payment_Date
                             });

---

Related Topics