Linq

Getting results from Linq

Get results from table

using System.Linq;
public static List<Customer> GetByLoan(int loan)
{
  DC dc = new DC();
  IQueryable<Customer> customers = from cust in dc.lacmstrs join l in dc.llinks on cust.CustomerId equals l.CustomerId
                                   where l.LoanId == loan
                                   select cust;
  return customers.ToList();
}
// LINQ doesn't return a null-list, but a list with .Count = 0. So this will be the default.
// wildcard: IQueryable<lacmstr> customers = from cust in dc.lacmstr where cust.Name.Contains(name) select cust;

Outer joins

Get one record from table

customer cust = (from c in dc.customers where c.id == customerId select c).FirstOrDefault();  // or .First() or .Single() or .SingleOrDefault()

Single() operates on a collection of exactly one object and simply returns the object. First() operates on a collection of any number of objects and returns the first object. Take(1) operates on a collection of any number of objects and returns a collection containing the first object. …

Get results from stored procedure

public static List<csGetLoanCustomersResult> GetByLoan2(int loan)
{
  AmlDC aml = new AmlDC();
  IEnumerable<csGetLoanCustomersResult> customers = from cust in dc.csGetLoanCustomers(loan) select cust;
  return customers.ToList();
}

Execute stored procedure and get resulting recordset (eg. to get autoincrement id)

csTtaRun_I1Result run = (from r in dc.csTtaRun_I1(start, comment) select r).SingleOrDefault();

Get result in custom class

var query = from p in ctx.table1 select new YourCustomClass { field1 = p.fie1, field2 = p.fie2, field3 = p.tab2.fie3 };
or
var query = ctx.table1.Select(p => new YourCustomClass { field1 = p.fie1, field2 = p.fie2, field3 = p.tab2.fie3 });

Or: [http://www.iramellor.com/Custom-Entity-Classes-Using-LINQ-to-SQL-Part-1-DataObjects]

Get all child items in a list

IEnumerable<CreditApplicationAssetDetails> details = creditApplication.CreditApplicationAssets.SelectMany(x => x.CreditApplicationAssetDetails)

Inserting / updating

Add extra functionality

- Extensions
Nice use of Extensions: TimestampToString
- Partial classes
- Inheritance

public class ExtraFee : Foo
  {
    public bool extra { get; set; }
  }

Varia

Set timeout

dc.CommandTimeout = 300;

Synchronisation with database

http://www.codeproject.com/KB/linq/LINQ_to_SQL_Database_Sync.aspx

Load all child entities

http://stackoverflow.com/questions/315966/how-do-you-construct-a-linq-to-entities-query-to-load-child-objects-directly-ins

Errors

Expression of type ….List is not queryable.

Add
imports System.Linq / using System.Linq

The objects you are adding to the designer use a different data connection than the designer is currently using. Do you want to change the connection used by the designer?

You can find the used connection underneath the DataContext Properties: Connection.
These settings are kept in the Settings of the Project of the DataContext.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License