score:1

Accepted answer

you can use the following to display the generated sql for the linq statement.

reachdirectdatacontext rddc = new reachdirectdatacontext(connstring);

rddc.log = console.out;

return (from b in rddc.brokernos
        from p in dc.promotions     
        where p.source == source && p.broker == b.brokerno1
        select new promotion() {code=p.code,brokername=b.name}).tolist<promotion>();

you can try the following to separate out the queries.

var promotions = from p in dc.promotions
                 where p.source == source
                 select p;

var brokers = from o in promotions
              join b in rddc.brokernos on o.broker equals b.brokerno1
              select new promotion 
              {
                  code = o.code,
                  brokername = b.name
              };
return brokers.tolist();

score:0

the double from looks suspicious to me. (this is not the equivalent to sql's join statement, if that is what you were aiming for.)

if you can combine the contexts:

try creating relationship between brokernos and promotions in edmx and using navigation property in query.

for example:

var result = dc.promotions.where(p => p.source == source).
    select(p => new promotion() {
        code = p.code,
        brokername = p.broker.name, // use new navigation property here
    });

if not (intersection will be done in memory, not on db!!!:

var result1 = dc.promotions.where(p => p.source == source).
    select(p => new promotion() {
        code = p.code,
        brokerid = p.brokerid, // add id property for intermediate results
    }).tolist();   

var result2 = rddc.brokers.tolist();

var finalresult = result1.where(p => result2.contains(b => b.brokerid == p.brokerid)).select(p => new promotion{
        code = p.code,
        brokername = result2.single(b => b.brokerid == p.brokerid).name,
    });

score:2

your linq statement looks fine. to aid in debugging, i find it helpful to assign the linq query to a local variable, then return the local variable. you can then set a breakpoint on the return statement, and when the debugger stops at the breakpoint, you can inspect the query local variable to see what's in it, interactively. you can use the locals window in vs, or the immediate window to surf around inside your app's variables and see what's going on.

in particular, double check that the inputs into your linq query are actually providing data. verify that rddc.brokernos is non-empty, and dc.promotions, etc. if these are empty, the result will be empty. track your bug "upstream".

minor point: you don't need to specify the type parameter on the .tolist() call in the select. the compiler will infer the type automagically.


Related Query

More Query from same tag