none
Multiple table select & brain spasm RRS feed

  • Question

  • Selecting specific columns from multiple tables is easy. But what I want is the whole row from Table A along with a couple columns from Table B and I'm doing something wrong. What I've got is this:

     

    Code Snippet

    var issues = (from p in dc.DTC_Trading

    join jp in dc.Depositories on p.DepositoryID equals jp.DepositoryID

    where p.CloseOut == false

    && p.Settle1.Value.Date > DateTime.Now.Date

    && !(from o in dc.BloombergUploads

    select o.dtcTradingID).Contains(p.ID)

    select new

    {

    p,

    jp.DName,

    jp.DCity,

    jp.DState,

    jp.Restrictions

    }).ToList();

     

     this.dataGridView1.DataSource = issues;

     

     

     

    This looks right to my brain but it doesn't work the way I thought it would. Rather than give me the fields of p,

    it just gives me a "p" column that displays the row type.

     

    I realize I could just type out p.field1 ... p.fieldX in the select but 1) there are a lot of columns in that table and 2) I'm lazy and I'm sure there's something in LINQ that supports my laziness.

    Friday, June 13, 2008 6:26 PM

All replies

  • I suspect that trying to bind the object 'p' to a column in the GridView is resulting in the behavior you are observing.

     

    Can you confirm that the query itself is returning the entire DTC_Trading object?

     

    Thanks.

    --Samir

     

     

    Friday, June 13, 2008 8:28 PM
  • Hi Samir,

     

    Sorry, I should have mentioned that. Yes; after executing the query I can enumerate the 'p' and the contents of the row are there, as in:

     

    foreach ( var v in issues )

    {

    v.p.<ColumnName>...

    }

     

    So I guess what I'm wondering is, is there a way to phrase the original query so that I can essentially say "Give me all of the row from Table A and these specific fields from Table B", in such a way that the selections from both tables end up in the "top level" of the returned item?

     

    Spelled out I guess it would be something like

     

    select row of A

    AND

    select new

    {

    B.field1

    B.field2

    }

     

    foreach( var v in issues )

    {

    v.<column from A>...

    v.<column from B>...

    }

     

    I tried applying Union() but it got angry with me.

     

    I know it would be easy enough to run through the query results and manually stuff the fields into a new, single-level list of items that could be databound but it seems like there would be a way to do this.

    Friday, June 13, 2008 8:48 PM