LINQでサブクエリを行うには？

質問

私がLINQに変換しようとしているクエリの例です。

SELECT *
FROM Users
WHERE Users.lastname LIKE '%fra%'
    AND Users.Id IN (
         SELECT UserId 
         FROM CompanyRolesToUsers 
         WHERE CompanyRoleId in (2,3,4) )

の間にはFK関係があります。 CompanyRolesToUsers と Users といった具合に、多対多の関係でありながら CompanyRolesToUsers が接続テーブルです。

すでにサイトの大部分は構築されており、PredicateExtensionsクラスを使用してExpressionsを構築することで、すでにほとんどのフィルタリングが動作しています。

素直なフィルタのコードは以下のような感じです。

 if (!string.IsNullOrEmpty(TextBoxLastName.Text))
 {
     predicateAnd = predicateAnd.And(c => c.LastName.Contains(
                                     TextBoxLastName.Text.Trim()));
 }

e.Result = context.Users.Where(predicateAnd);

別のテーブルのサブセレクトに述語を追加しようとしています。( CompanyRolesToUsers )

追加できるようにしたいのは、このようなことをするものです。

int[] selectedRoles = GetSelectedRoles();
if( selectedRoles.Length > 0 )
{
    //somehow only select the userid from here ???:
    var subquery = from u in CompanyRolesToUsers
                   where u.RoleID in selectedRoles
                   select u.UserId;

    //somehow transform this into an Expression ???:
    var subExpression = Expression.Invoke(subquery);

    //and add it on to the existing expressions ???:
    predicateAnd = predicateAnd.And(subExpression);
}

このような方法はないのでしょうか？ストアドプロシージャは簡単に書けるのですが、LINQというのは初めてで、しかも締め切りがあるので、もどかしいです。一致する例を見つけられませんでしたが、どこかにあるはずです。

どのように解決するのですか？

サブクエリを使ってみましょう。

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)
)

この部分の質問について。

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

クエリをオーサリングする前にテキストボックスから文字列を抽出することを強くお勧めします。

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

データベースに送信される内容をきちんと管理したいものです。 元のコードでは、トリミングされていない文字列がトリミングのためにデータベースに送信されるということがあり得ますが、これはデータベースが行うべき良い仕事ではありません。