none
left outer join with many properties RRS feed

  • Question

  • I have a join that I am using right now that takes two fairly large tables and joins them on a common column.  What I need to know is if it is possible to do this without listing every column I need in the select new{} section of the join.  To further illustrate here is an example of what I need to do.

    var resultsQuery = from d in dataEntities.members.Where(d => d.first_name.StartsWith(lookup_FirstName.Text) && d.last_name.StartsWith(lookup_LastName.Text) &&
              d.id.StartsWith(lookup_ID.Text) && d.ssn.StartsWith(lookup_SsNumber.Text)).DefaultIfEmpty()
              from a in dataEntities.member_address.Where(a => a.id == d.id).DefaultIfEmpty()
              select new { item1 = d.item1, item2 = d.item2,..., itemn = d.itemn, aitem1 = a.item1, aitem2 = a.item2,..., aitemn = a.itemn
              };

    So basically what I don't want to do i have to type out 40 or so different properties for the joined table a bunch of times in order to get all of the information I will need anyway.  Is this possible?

     

    Thanks,

    Joe

    Monday, May 10, 2010 6:14 PM

Answers

  • You could always create a class that take the two entity types and that expose the members you want to display in the grid. E.g.:

    internal class SomeQueryResult
    {
      //L2S entities
      public Member Member { get;set; }
      public MemberAddress MemberAddress { get;set; }

      //grid visible members
      public string MemberName { get { return Member.Name; } private set {} }
      public string Description { get {return Member.Description; } private set {} }
      public string MemberAddressLine1 { get {return MemberAddress.Line1; } private set {}}
      public string MemberAddressLine2 { get {return MemberAddress.Line2; } private set {}}
      public string MemberAddressLine3 { get {return MemberAddress.Line3; } private set {}}
    }

    ...and then in your query:

    select new SomeQueryResult { Member = d, MemberAddress = a };


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    • Proposed as answer by liurong luo Friday, May 14, 2010 7:40 AM
    • Marked as answer by jmanier Friday, May 14, 2010 1:55 PM
    Wednesday, May 12, 2010 3:13 AM
    Answerer

All replies

  • So, I think I need to clarify my problem a little bit since there hasn't been any responses to this question. 

    As above I have a query that joins two tables with a key that is the same between the two.  The resulting list is then bound to a datagrid for display.  The datagrid only displays some of the actual date e.g. a general description.  When the user selects one of the rows in the datagrid the value is stored in a temporary variable that is used as the datacontext for the more detailed veiw screen.  This is where the problem comes in.  In order to be able to edit the values I require two way binding capabilities, but the query returns a read-only anonymous type.  I have tried creating an "answer" table in my db to store this query in but that throws a different error as it is a complex data type.

    My question is, what is the best way to join two tables from a db with linq to sql and still be able to have a two way binding for a datacontext in a wpf app.

    Tuesday, May 11, 2010 2:06 PM
  • You could always create a class that take the two entity types and that expose the members you want to display in the grid. E.g.:

    internal class SomeQueryResult
    {
      //L2S entities
      public Member Member { get;set; }
      public MemberAddress MemberAddress { get;set; }

      //grid visible members
      public string MemberName { get { return Member.Name; } private set {} }
      public string Description { get {return Member.Description; } private set {} }
      public string MemberAddressLine1 { get {return MemberAddress.Line1; } private set {}}
      public string MemberAddressLine2 { get {return MemberAddress.Line2; } private set {}}
      public string MemberAddressLine3 { get {return MemberAddress.Line3; } private set {}}
    }

    ...and then in your query:

    select new SomeQueryResult { Member = d, MemberAddress = a };


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    • Proposed as answer by liurong luo Friday, May 14, 2010 7:40 AM
    • Marked as answer by jmanier Friday, May 14, 2010 1:55 PM
    Wednesday, May 12, 2010 3:13 AM
    Answerer