score:5

Accepted answer

if your test entity has a usertests collection you can use this query:

string userid = "1";
var result = context.tests
    .selectmany(t => t.usertests
        .where(ut => ut.userid == userid)
        .defaultifempty()
        .select(ut => new
        {
            examid = t.examid,
            testid = t.testid,
            title = t.title,
            usertestid = (int?)ut.usertestid,
            userid = ut.userid,
            result = ut.result
        }))
    .orderby(x => x.examid)
    .thenby(x => x.testid)
    .thenby(x => x.usertestid)
    .tolist();

using defaultifempty() here ensures a left outer join so that you always have at least one usertest entity (which is possibly null) for a given test. casting the non-nullable properties of the usertest - like usertestid - to a nullable type (int? for example) is important here, otherwise you can get an exception that a null value returned from the database can't be stored in a non-nullable .net type.

if you don't have and don't want a usertests collection in you test entity you can use a groupjoin as alternative which would basically left outer join the two tables by the testid:

string userid = "1";
var result = context.tests
    .groupjoin(context.usertests.where(ut => ut.userid == userid),
        t => t.testid,
        ut => ut.testid,
        (t, utcollection) => new
        {
            test = t,
            usertests = utcollection
        })
    .selectmany(x => x.usertests
        .defaultifempty()
        .select(ut => new
        {
            examid = x.test.examid,
            testid = x.test.testid,
            title = x.test.title,
            usertestid = (int?)ut.usertestid,
            userid = ut.userid,
            result = ut.result
        }))
    .orderby(x => x.examid)
    .thenby(x => x.testid)
    .thenby(x => x.usertestid)
    .tolist();

score:0

score:0

here is a link to a discussion that shows how to call stored procedures with a parameter: how to use dbcontext.database.sqlquery<telement>(sql, params) with stored procedure? ef code first ctp5

here is one way to code the stored procedure: 

create procedure dbo.sample1 (
@oneid nvarchar(128) = n'xx') as
begin
set nocount on;

select @oneid as userid,
    r.testid, 
    r.result
from (
    select t.userid, e.testid, t.result
    from dbo.usertest as e
    left outer join dbo.usertest as t on e.testid = t.testid and t.userid = @oneid
    where  e.userid = 0) as r 
order by r.testid 

end 
go

score:0

try this:

var tests = context.tests.include( "exam" )
    .select( t => new
    {
        test = t,
        usertests = t.usertests.where( ut => ut.userid == studentid )
    } )
    .tolist();

score:1 

 var tests = (from t in context.tests
       // where !t.userttests.any() //if no user took the test
         //    || t.usertests.any(ut=>ut.student.studentid == stid)
        select new {test = t, exam = t.exam, 
                 usertests = t.usertests.where(ut=>ut.student.studentid == stid))
       .tolist();

on 2nd thought, may be this will be better. this will give you exam, test and usertests if there is any matching ones or null usertests


Related Query

More Query from same tag