linq query from more tables

Question

public ActionResult myItems() {

        var dataContext = new RecordsDataContext();
        MembershipUser myObject = Membership.GetUser();         
        string CurrentUserName = myObject.UserName.ToString();    

       var user = from i in dataContext.myUsers
                   where i.userName ==CurrentUserName
                   select i.id;

        var items=from j in dataContext.OtherUsers
                   where j.id_user==user   /*error:operator '==' cannot be aplied to operands of type 'int' and 'System.Linq.Iquerable<int>'*/
                   select j;



           return View(items);

    }

Pls help me with this error

Answer 1

Actually user from the first query is not a single int result. You need to check if user.Count() is not 0 and you could use user.First() to retrieve first row result.

But I suggest to use join (something like that):

public ActionResult myItems() {
    var dataContext = new RecordsDataContext();
    var query = from i in dataContext.myItems
                join ou in dataContext.OtherUsers
                on i.id_user equals ou.id_user //check real reference, since I don't know
                join mu in dataContext.myUsers
                on ou.id_user equals mu.id_user
                where mu.username == Membership.GetUser().UserName.ToString()
                select i;
    return View(query);
}

Answer 2

public ActionResult myItems() {

var dataContext = new RecordsDataContext();
MembershipUser myObject = Membership.GetUser();         
string CurrentUserName = myObject.UserName.ToString();    

var user = from i in dataContext.myUsers
           where i.userName == CurrentUserName
           select i.id;
if (user.Count == 1) //found one
{
    var items = from j in dataContext.OtherUsers
                where j.id_user == user.First()
                select j;
    //return View(items);
  var temp = from f in dataContext.myItems
                  where f.id_items == items.First()
                  select f;
        return View(temp);//my new error :) this is return only 1 result, how I can get other
}

return View(new ActionResult());

}

Thanks Viktor for previous answer, that is it