title: linq join on guid and string column
body: <p>I'm new to linq. I need to run a query that joins two columns (<code>AnonymousUser.AnonymousId</code> being <code>uniqueidentifier</code> and comment.UserId being <code>nvarchar(100)</code>), something like below:</p>
  
  <pre><code>        using (CommentEntities db = new CommentEntities())
          {
              // filteredComments is a query that is not run until the next .ToList()
              IQueryable&lt;Comment&gt; filteredComments = this.CommentGetList(...);
              var query = from comment in filteredComments
                           // following line is the syntax error, because columns' types don't match
                           join user in db.AnonymousUsers on comment.UserId equals user.AnonymousId into gj
                           from userNull in gj.DefaultIfEmpty()
                           select new CommentWithName
                           {
                               Comment = comment,
                               UserId = comment.UserId,
                               FirstName = (userNull == null ? "" : userNull.Name),
                               LastName = "",
                               Email = (userNull == null ? "" : userNull.Email)
                           };
              return query.ToList();
          }
  </code></pre>
  
  <p>First I was happy writing the query with <code>.ToString()</code> ! As it turns out that entity framework doesn't know how to translate it to sql. The same is true for <code>Guid.Parse(string)</code>. Also <code>new Guid(string)</code> cannot be used in linq to entities (only parameterless constructors allowed)!</p>
  
  <p>So after searching, I found out it's not possible doing such thing in EF 4.0! I migrated my code to a stored procedure that I'm not really happy about it.</p>
  
  <p>Is it possible to tell entity framework to use a <code>CAST</code> in SQL?</p>
  
  <p>Is there any solutions to this problem? Is there any way that I can bring the logic in code?</p>
  
  <p><strong>NOTE</strong>: I meant to do it in <strong>one GO</strong>. Otherwise one possible solution is to get Entities from first table, and put the Ids in a list and get entities from second table.</p>