IQueryable left outer?

[Techno]

using .NET 3.5

I am trying to use IQueryable here and dont want to have to convert to a list/collection in order to do something of the following:

I am querying a table. it has UserID.
I want to be able to do a left outer join from the Users table so I can obtain Username.

how can I do this?

currently, the objects I have expose UserId, which is fine, and then I want to be able to obtain Username at some stage and populate it into an IQueryable entity which I have (which 99% has the same properties as my other objects with this one exception which is what I need)

[jmcilhinney]

Can the UserID in that table be NULL?

[Techno]

yes in this case because the "history" table has a nullable user ID column

[jmcilhinney]

E.g.

var parents = Enumerable.Range(1, 5)
                        .Select(n => new { ParentID = n, ParentName = "Parent" + n })
                        .ToArray();
var children = Enumerable.Range(1, 5)
                         .Select(n => new
                                          {
                                              ChildID = n,
                                              ParentID = (n % 2 == 0 ? (int?)null : n),
                                              ChildName = "Child" + n
                                          })
                         .ToArray();

var innerResults = from c in children
                   join p in parents
                       on c.ParentID equals p.ParentID
                   select new
                              {
                                  c.ChildName,
                                  p.ParentName
                              };

foreach (var result in innerResults)
{
    MessageBox.Show(string.Format("{0}, {1}",
                                  result.ChildName,
                                  result.ParentName),
                    "Inner Join");
}

var outerResults = from c in children
                   join p in parents
                       on c.ParentID equals p.ParentID into joinedParents
                   from jp in joinedParents.DefaultIfEmpty()
                   select new
                              {
                                  c.ChildName,
                                  ParentName = (jp == null ? string.Empty : jp.ParentName)
                              };

foreach (var result in outerResults)
{
    MessageBox.Show(string.Format("{0}, {1}",
                                  result.ChildName,
                                  result.ParentName),
                    "Outer Join");
}

In the second query, 'joinedParents' is basically the same series as 'parents' but with default, i.e. null, values inserted where there is a child with no corresponding parent. Because joinedParents can contain nulls, we must check for them in the 'select' clause and provide our own default value where they are found.

[Techno]

Thanks. I will try this and should hopefully work with the IQueryable as I am using LINQ -> SQL.

tables:

History
ID
Column1
Column2
CreatedAt
UserID (FK Nullable)


User:

ID
Name
Username
CreatedAt

[Techno]

ok, some more background.

im using, unfortunately, a 3rd party telerik control. We are doing an async call back to filter/search through an IQueryable of results.

the actual data itself, is contained in 2 tables. I then create a "flat" class, which just grabs the values from both tables/classes and put them into one class (just a class with get/set properties)

problem is that when we do a sort/filter on a column which does not exist on the one table, everything falls, because the column does not exist on the table it is querying (History table)

not sure how to get around this problem. and its a little tricky to explain also.