Linq query to get all pending Loans

Question

I have two tables in lightswitch LOANS(Id(default),..) and RELEASES(Id(default),Loan,..).i want to create a screen with all pending loans to be shown in a datagrid.so i created a wcf RIA class library.i wanto return all the loans that have no releases yet.what would be the linq query for that. i tried this from other SO post but it gave a null reference exception.Nullreference exception was unhandled by user code.Object reference not set to an instance of object

Loan to Release has 1 : 0/1 (one loan to zero or one release)relationship a loan can have zero or one relationship.a release must have a loan.

 [Query(IsDefault = true)]
    public IQueryable<PendingLoans> GetPendingLoans()
    {
        var res = from l in this.context.Loans
                  join r in this.context.Releases
                  on l equals r.Loan
                  where r.Loan == null
                  select new PendingLoans { BillNo = l.BillNo };
        return res.AsQueryable<PendingLoans>();
    }

how can anyone answer this when u dont even give the table definitions. you need to list all columns/relationships. Why would release have Id and ReleaseId? Have you created a relation on Releases? If you set up relations you dont need a Join, and Lightswitch should pretty much take care of a simple query like this without needing to code manual queries that`s what it`s for. That query makes little sense to be honest you are asking to return Loans but then stipulating that only NULL Loan — Mark Homer, Jul 20 '13 at 17:16
@MarkHomer Homer i have edited to add relations info how do suggest the easy way to do this in lightswitch. — Rakon_188, Jul 20 '13 at 18:07
been awhile since I used LightSwitch not got it installed at the moment but I wouldn't do this for you anyway. I think a better approach at this point is reading-up on Databases and designing your tables and relations. Pointless copying and pasting queries off here. — Mark Homer, Jul 20 '13 at 19:00

nerdybeardo · Answer 1 · 2013-07-20T07:47:12.750

0

Try this, this is linq but using lambdas instead of the query syntax

[Query(IsDefault = true)]
public IQueryable<PendingLoans> GetPendingLoans()
{
    var res = this.context.Loans.Where(l=>!l.Releases.Any()).Select(l=> new PendingLoans { BillNo = l.BillNo }).AsQueryable();
    return res;
}

If you want to use the query syntax this is effectively the same thing

[Query(IsDefault = true)]
public IQueryable<PendingLoans> GetPendingLoans()
{
    var res = from l in this.context.Loans
              where !l.Releases.Any()
              select new PendingLoans { BillNo = l.BillNo };
    return res.AsQueryable();

}

Now one thing I will say is that because you're converting to a PendingLoan before you say "AsQueryable", you've already enumerated over your data set (converting from one type of object to another). Therefore this will not have the same benefits of late bindings you may be trying to get out of the "AsQueryable" portion (You've already executed against the db), so you may be better off just returning an IEnumerable and forgetting about the AsQueryable part since you've already enumerated once.

edited Jul 20 '13 at 07:47

answered Jul 20 '13 at 07:24

nerdybeardo

4,655
23
32

i just tried that but get error(red underline on)l=>!l.Releases.Any() saying delegate 'System Func' doesnot take one argument – Rakon_188 Jul 20 '13 at 07:30
basicallly it says doesnot contain definition for ANY() – Rakon_188 Jul 20 '13 at 07:31
@Rakon_188 are you using Entity Framework or linq to sql or something else? Also if you can edit your question to add your data structure (your tables and their primary keys) – nerdybeardo Jul 20 '13 at 07:42
EF i guess,pls bear with me i am new i really dont know.its an light switch application with tables and screens.to perform data aggregation i attached a wcf ria class to it.this particular code is from that class. – Rakon_188 Jul 20 '13 at 07:47
ur help really appreciated – Rakon_188 Jul 20 '13 at 08:05

Linq query to get all pending Loans

1 Answers1