-1

This is only work for existing Customer in Ledger Table:

var i = (from u in dc.GetTable<Ledger>()
         where u.C_Id == u.Customer.Id &&
         u.Customer.Name == textBox1.Text
         select u.Id).Max();

This is only work for new Customer in Ledger Table:

var i = (from u in dc.GetTable<Ledger>()
         where u.C_Id == u.Customer.Id &&
         u.Customer.Name == textBox1.Text
         select u.Id).SingleOrDefault();

System.InvalidOperationException occurred in System.Data.Linq.dll

Provide me the solution for both new and existing customer in Ledger Table. what is the query for getting Ledger Id for both case.

Asad Rasool
  • 1
  • 2
  • 1
    Possible duplicate of [Max return value if empty query](https://stackoverflow.com/questions/6966680/max-return-value-if-empty-query) – mjwills Nov 30 '17 at 13:26
  • 2
    Does changing `SingleOrDefault` to `FirstOrDefault` help? – mjwills Nov 30 '17 at 13:27
  • yes but isn't working `FirstOrDefault()` for both cases. – Asad Rasool Nov 30 '17 at 13:32
  • `SIngleOrDefault()` works well for new customer in ledger and `Max()` works well for existing customer in ledger table. – Asad Rasool Nov 30 '17 at 13:34
  • In searches such as this, based on user input, when you never know, will it return data or not, you never use `SingleOrDefault` or `Max`. Always use `FirstOrDefault`. Even that doesn't make sense since you [probably] want to display all results that returned by query – T.S. Nov 30 '17 at 15:42

2 Answers2

0

I'm not sure why you want the highest ID if their are multiples but this should work

var ledger = 
     dc.GetTable<Ledger>()
     .Where(l => l.C_Id == l.Customer.Id)
     .Where(l => l.Customer.Name == textBox1.Text)
     .Select(l => l.Id)
     .OrderByDescending(id => id)
     .FirstOrDefault();
Mant101
  • 2,705
  • 1
  • 23
  • 27
0

Finally I got the right query! This is the query for getting ledger Id for both new or existing customer in Ledger Table

var i = (from w in dc.GetTable<Customer>() //customer id
         where w.Name == textBox1.Text
         select w.Id).SingleOrDefault();

int? j = dc.Ledgers.Where(x => x.C_Id == i)
                   .Max(x => (int?)x.Id);
Asad Rasool
  • 1
  • 2
  • Thats two queries and the first should throw an exception if you get more than one matching name. You said there are many ids for the customer, have you tested it with data where the first part returns multiple records? – Mant101 Nov 30 '17 at 17:32
  • I give you (F-) for this solution – T.S. Nov 30 '17 at 18:42