Joseph Michael Pesch
VP Programming

Linq Child/Nested Entity Join to Master Table Entity

by 26. May 2011 10:13
    private void LoadList_AssignedResources(int RequestId)
      using (SPMetalDataContext dc = new SPMetalDataContext(this.Web.Url))

        ResourceRequestsItem req = dc.ResourceRequests
          .Where(x => x.Id.Equals(RequestId)).FirstOrDefault();
        var qry = from rs in dc.Resources
                  join rx in req.Resources on rs.Id equals rx.Id
                  select new
                    Title = rs.Title,
                    Id = rs.Id

        AssignedResourcesList.DataTextField = "Title";
        AssignedResourcesList.DataValueField = "Id";
        AssignedResourcesList.DataSource = qry;


C# | LINQ to SQL

Linq To SQL Error: Cannot access a disposed object.

by 17. March 2010 14:06

Cannot access a disposed object. Object name: 'DataContext accessed after Dispose.'.  This can happen if you send a Linq query result entity out of a procedure as shown below.  The issues is that the inner procedure has not actually evaluated the query (i.e. results have not been retrieved from the SQL server, just the construct for the query exists).  When you leave the inner procedure and attempt to access the data within the outer procedure the database context is gone (since it was wrapped in a using statement).  Only at the point when you attempt to access the data members will the actual query results be requested from the SQL server and at that point there is no database context under which to execute the query.  You may get the process to work by removing the using statement; however, that is not a correct practice.  The better solution is to return an IEnumerable list using the .ToList() method on the result entity you are returning.  This will force the query to execute and the full result set will be populated into the returned list.

Outer Procedure Code:
   IEnumerable<BidMortgage.BMObject.LowestBid> bids = BidMortgage.BMObject.GetLowestBids();

   /* Error here if not using .ToList<>() */
   foreach (BidMortgage.BMObject.LowestBid bid in bids)


Inner Procedure Code:

public class LowestBid


   public LowestBid()



   public int LoanTypeID { get; set; }

   public string LoanTypeName { get; set; }

   public decimal CalculatedAPR { get; set; }

   public decimal InterestRate { get; set; }




public static IEnumerable<LowestBid> GetLowestBids()


  DataTable tbl = new DataTable();



    using (BidMortgage.BidMortgageDataContext db = new BidMortgageDataContext())

    return (from row in db.LoanRequestBids

    group row by row.tbl_LoanRequest.LoanTypeID

    into g

    select new LowestBid

    { LoanTypeID = g.Key

      , LoanTypeName = (from itm in db.ListValues_MortgageTypes

                        where itm.TypeValue == g.Key

                        select itm.TypeName).SingleOrDefault()

      , CalculatedAPR = g.Min(t => t.CalculatedAPR)

      , InterestRate = (from itm in db.LoanRequestBids

                        where itm.CalculatedAPR == g.Min(t => t.CalculatedAPR)

                        && itm.tbl_LoanRequest.LoanTypeID == g.Key

                        select itm.InterestRate).SingleOrDefault()


     If you don't use the .ToList<LowestBid>() and just return the select entity you will
     receive this error when attempting to access the data outside of this using statement:
     Cannot access a disposed object. Object name: 'DataContext accessed after Dispose.'.


  catch (Exception)


    return null;





ASP.Net | C# | LINQ to SQL

LINQ to SQL with WCF and Silverlight

by 7. February 2010 19:27

Tags: ,

ASP.Net | LINQ to SQL | Silverlight

Binary Data Serialization LINQ to SQL via Web Service

by 7. February 2010 18:17

In summary, one easy fix is manually change the LINQ data type from Binary (System.Data.Linq.Binary) to byte[] (System.Byte[])

Tags: ,


LINQ Dynamic Order By on IQuerable Object

by 2. November 2009 23:24

private IQueryable<ClientInfo> OrderBy(IQueryable obj){

  ParameterExpression param = Expression.Parameter(typeof(ClientInfo), string.Empty);

  MemberExpression property = Expression.PropertyOrField(param, this.SortExpressionLabel.Text);  

  LambdaExpression sort = Expression.Lambda(property, param); 

  string methodName = "OrderBy"; 

  if (SortDirectionLabel.Text == "DESC")  methodName += "Descending"; 

  MethodCallExpression call = Expression.Call(typeof(Queryable), methodName, new[] { typeof(ClientInfo), property.Type },      obj.Expression, Expression.Quote(sort)); 

  return obj.Provider.CreateQuery<ClientInfo>(call);


Tags: ,


Linq to SQL Schema Refresh (SQLMetal.exe & SQLMetalOSUI)

by 26. April 2009 20:13

SQLMetal.exe is a command line tool that will build Linq to SQL *.dbml code files. (file location: %ProgramFiles%\Microsoft SDKs\Windows\v6.0A\Bin\SQLMetal.exe)

SQLMetalOSUI (Open Source User Interface) is an open source UI for running SQLMetal.exe

SQLMetal.exe Parameters:

Microsoft (R) Database Mapping Generator 2008 version 1.00.30729
for Microsoft (R) .NET Framework version 3.5
Copyright (C) Microsoft Corporation. All rights reserved.
SqlMetal [options] [<input file>]
Generates code and mapping for the LINQ to SQL component of the .NET framework. SqlMetal can:
- Generate source code and mapping attributes or a mapping file from a database.
- Generate an intermediate dbml file for customization from the database.
- Generate code and mapping attributes or mapping file from a dbml file.
/server:<name> Database server name.
/database:<name> Database catalog on server.
/user:<name> Login user ID (default: use Windows Authentication).
/password:<password> Login password (default: use Windows Authentication).
/conn:<connection string> Database connection string. Cannot be used with /server, /database, /user or /password options.
/timeout:<seconds> Timeout value to use when SqlMetal accesses the database (default: 0 which means infinite).
/views Extract database views.
/functions Extract database functions.
/sprocs Extract stored procedures.
/dbml[:file] Output as dbml. Cannot be used with /map option.
/code[:file] Output as source code. Cannot be used with /dbml option.
/map[:file] Generate mapping file, not attributes. Cannot be used with /dbml option.
/language:<language> Language for source code: VB or C# (default: derived from extension on code file name).
/namespace:<name> Namespace of generated code (default: no namespace).
/context:<type> Name of data context class (default: derived from database name).
/entitybase:<type> Base class of entity classes in the generated code (default: entities have no base class).
/pluralize Automatically pluralize or singularize class and member names using English language rules.
/serialization:<option> Generate serializable classes: None or Unidirectional (default: None).
/provider:<type> Provider type: SQLCompact, SQL2000, SQL2005, or SQL2008. (default: provider is determined at run time).
<input file> May be a SqlExpress mdf file, a SqlCE sdf file, or a dbml intermediate file.
Create code from SqlServer:
SqlMetal /server:myserver /database:northwind /code:nwind.cs /namespace:nwind
Generate intermediate dbml file from SqlServer:
SqlMetal /server:myserver /database:northwind /dbml:northwind.dbml /namespace:nwind
Generate code with external mapping from dbml:
SqlMetal /code:nwind.cs / northwind.dbml
Generate dbml from a SqlCE sdf file:
SqlMetal /dbml:northwind.dbml northwind.sdf
Generate dbml from SqlExpress local server:
SqlMetal /server:.\sqlexpress /database:northwind /dbml:northwind.dbml
Generate dbml by using a connection string in the command line:
SqlMetal /conn:"server='myserver'; database='northwind'" /dbml:northwind.dbml

Tags: ,


C# Convert Enum Array Into Typed Array Used in LINQ to SQL IN Query

by 30. March 2009 17:22

The example below take an enum array input variable, converts to an integer array variable for use in a LINQ to SQL IN clause type query:

    public enum FlagTypeCode
      NotAssigned = 0,
      SendToServicerBlocked = 100,
      SelectedForQCReview = 200
    public static IQueryable<ModFlagDate> LoadModFlagDate(FlagTypeCode[] FlagTypeCodeList, ref string ErrorMessage)
      int[] FlagTypeList = Array.ConvertAll(FlagTypeCodeList, value => (int) value);
      ModCalcDataContext db = new ModCalcDataContext();
      var rows = (from row in db.ModFlagDates
                        where FlagTypeList.Contains(row.FlagTypeCode)
                        select row);
      return rows;

Tags: ,

C# | LINQ to SQL

LinqSql DBML Connection String from Web.Config

by 26. March 2009 14:38

By default the DBML object sets the connection string information on the properties tab of the designer (available when you click on the white space in the the ORM designer).  IMPORTANT NOTE: Although you can select a web.config entry from the drop-down list in the designer, it will actually store the detailed connection string information in the DBML.cs file and not actually re-read the web.config at run time.  To use a true dynamic web.config default (vs. this hard coded default) make sure the designer connection is blank as shown in screen shot below.

Add partial class like this: 

namespace CRM {
publicpartialclassCRMDataContext : System.Data.Linq.DataContext

public CRMDataContext() : base(System.Configuration.ConfigurationManager.ConnectionStrings["CRM"].ConnectionString, mappingSource)

Tags: ,


LINQ to SQL Error: Cannot access a disposed object.

by 3. October 2008 14:48

LINQ to SQL Error:

Cannot access a disposed object.
Object name: 'DataContext accessed after Dispose.'.

One case that case cause this error is when related table/object doesn't have any rows and the parent object is used outside of the scope of the DataContext object that instantiated it.


public static tbl_LoanRequest GetLoanRequest(Guid LoanRequestID)
  using (BidMortgageDataContext bmdb = new BidMortgageDataContext())
    tbl_LoanRequest LoanRequestTable = bmdb.tbl_LoanRequests.SingleOrDefault(r => r.LoanRequestID == LoanRequestID);
    if (LoanRequestTable == null)
      LoanRequestTable = new tbl_LoanRequest();
    else if (LoanRequestTable.LoanRequestBids.Count() == 0)
      LoanRequestTable.LoanRequestBids = new System.Data.Linq.EntitySet<LoanRequestBid>();  // This line prevents the error.
    return LoanRequestTable;