LINQ Examples and Errors

LINQ - Join Example (C#)

return (from xfs in dc.tblXRefFrameSpecifications
       join frame in dc.tblFrames on xfs.uiFrameFK equals frame.uiFramePK
       join framecolor in dc.tblFrameColors on xfs.uiFrameColorFK equals framecolor.uiFrameColorPK
       join frametype in dc.tblFrameTypes on xfs.uiFrameTypeFK equals frametype.uiFrameTypePK
       join lensmaterial in dc.tblLensMaterials on xfs.uiLensMaterialFK equals lensmaterial.uiLensMaterialPK
       join lenstype in dc.tblLensTypes on xfs.uiLensTypeFK equals lenstype.uiLensTypePK
       join temple in dc.tblTemples on xfs.uiTempleFK equals temple.uiTemplePK
     select new XrefFrameSpecs
     {
         XRefFrameSpecificationGUID = xfs.uiXRefFrameSpecificationPK.ToString(),
         FrameType = frametype.vcFrameTypeDescription,
         LensMaterial = lensmaterial.vcDescription,
         LensType = lenstype.vcLensTypeDescription,
         Temple = temple.vcTempleDescription,
         FrameDescription = frame.vcFrameDescription,
         FrameColor = framecolor.vcFrameColor,
         BridgeSize = xfs.vcBridgeSize,
         EyeSize = xfs.vcEyeSize
     //}).ToList();
     }).Where(pc => pc.BridgeSize.Equals("24")).Where(pc => pc.EyeSize.Equals("52")).ToList();

LINQ - Insert Example (C#)

using (SecurityDataContext dc = new SecurityDataContext())
     {
     tblSecurity_AuthorizedUser newUser = new tblSecurity_AuthorizedUser();
     newUser.bIsActive = true;
     newUser.SSN = "123456789";
     
     dc.tblSecurity_AuthorizedUsers.InsertOnSubmit(newUser);
     dc.SubmitChanges();
     }

LINQ - Update Example (C#)

using (SecurityDataContext dc = new SecurityDataContext())
     {
     var thisUser = (from p in dc.tblSecurity_AuthorizedUsers select p).First();
     thisUser.bIsActive = false;
     dc.SubmitChanges();
     }

LINQ - Delete Example (C#)

using (SecurityDataContext dc = new SecurityDataContext())
     {
     var thisUser = (from p in dc.tblSecurity_AuthorizedUsers 
                     where p.SSN.Equals("123456789")
                     select p).First();
     dc.tblSecurity_AuthorizedUsers.DeleteOnSubmit(thisUser);
     dc.SubmitChanges();
     }

LINQ - Where Example (C#)

// Create a Base Query
var WorkloadCCRFromDB = (from p in dc.tblWorkloadCCRs select p)             // Not Executed Yet

// Add a Where Clause to the query
WorkloadCCRFromDB = WorkloadCCRFromDB.Where(pc => pc.iWorkloadCCRPK.Equals([WorkLoadCCRPK type value)]);     // still not executed yet

// Add another Where Clause to the query
WorkloadCCRFromDB = WorkloadCCRFromDB.Where(pc => pc.fieldname2.Equals([FieldName2 type value)]);     // still not executed yet

// Execution of the query is delayed until an evaluation must be made 
// on the dynamic contents of the results of the query
if (WorkloadCCRFromDB.Any())        // .Any() Method actually Executes the query and evaluates .Any() condition
    return WorkloadCCRFromDB;

LINQ - .Contains Example and Problem with CHAR Comparisons (VB)

'<<<<<<<<<<<<<<<<< Listing 1 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
' The code block below will fail because of an apparent LINQ bug that doesn't interpret
' the CHAR values in ValidBeginCodes array as a value in the SQL statement that needs single quotes.

Dim ValidBeginCodes As Char() = {"P"c, "M"c}
Dim dc As New CMISNightlyProcessingDataContext()

Dim CFR = (From p In dc.Client_FinancialRestrictions _
           Where p.PK_Financial = PKFinancial _
           And ValidBeginCodes.Contains(p.BeginCode) _
           And p.ActiveDate.HasValue() _
           And Not p.EndDate.HasValue() _
           Select p) 

SELECT [t0].[PK_Financial], 
    [t0].[RecordKey], 
    [t0].[BeginCode], 
    [t0].[EnteredDate], 
    [t0].[ActiveDate], 
    [t0].[EndDate], 
    [t0].[EndCode], 
    [t0].[RecordLocked], 
    [t0].[CreatedBy], 
    [t0].[CreatedWhen], 
    [t0].[PrevUpdatedBy], 
    [t0].[PrevUpdatedWhen], 
    [t0].[CHANGEDBY], 
    [t0].[CHANGEDWHEN], 
    [t0].[ClientFinancialRestrictionsID]
FROM [dbo].[Client_FinancialRestrictions] AS [t0]
WHERE ([t0].[PK_Financial] = 289848) 
  AND (([t0].[BeginCode]) IN (P, M)) 
  AND ([t0].[ActiveDate] IS NOT NULL) 
  AND (NOT ([t0].[EndDate] IS NOT NULL))

'<<<<<<<<<<<<<<<<< Listing 2 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
' Changing the array type to String allows LINQ to create the proper SQL statement.

Dim ValidBeginCodes As String() = {"P", "M"}
Dim dc As New CMISNightlyProcessingDataContext()

Dim CFR = (From p In dc.Client_FinancialRestrictions _
           Where p.PK_Financial = PKFinancial _
           And ValidBeginCodes.Contains(p.BeginCode) _
           And p.ActiveDate.HasValue() _
           And Not p.EndDate.HasValue() _
           Select p) 

SELECT [t0].[PK_Financial], 
    [t0].[RecordKey], 
    [t0].[BeginCode], 
    [t0].[EnteredDate], 
    [t0].[ActiveDate], 
    [t0].[EndDate], 
    [t0].[EndCode], 
    [t0].[RecordLocked], 
    [t0].[CreatedBy], 
    [t0].[CreatedWhen], 
    [t0].[PrevUpdatedBy], 
    [t0].[PrevUpdatedWhen], 
    [t0].[CHANGEDBY], 
    [t0].[CHANGEDWHEN], 
    [t0].[ClientFinancialRestrictionsID]
FROM [dbo].[Client_FinancialRestrictions] AS [t0]
WHERE ([t0].[PK_Financial] = 261875) 
  AND ((CONVERT(NVarChar(1),CONVERT(NChar(1),[t0].[BeginCode]))) IN ('P', 'M')) 
  AND ([t0].[ActiveDate] IS NOT NULL) 
  AND (NOT ([t0].[EndDate] IS NOT NULL))

LINQ Error: String must be exactly one character long.

When working with Linq, you may get the following error:

String must be exactly one character long

The problem is in the Linq to SQL designer, and the way the code generator interpreted results from the table/view being used. This is a bug in the Linq generator that converts the Database TYPE nVarChar(1) or VarChar(1) to Linq CHAR(1) in the ???.designer.vb of the ???.dbml file. It occurs , specifically, because the nVarChar(1) field allows String.Empty ("") to be stored in it but a CHAR(1) field does not. When the linq query executes, the String.Empty value from the database cannot be populated in the correlating CHAR(1) Linq Property.

To remedy this, go back to the designer (open the ???.dbml file), pin the Properties sidebar open, and look at the elements which have been mapped. Any one which is mapped to Char(1) should be changed to a String.

Notes: This may impact table relationships (constrained on the field in question) AND stored procedures that return a table type (such as Appl_Financial) instead of an (Auto-generated Type). In the case where the change has these side-effects it may be necessary to either:

1. Leave the Linq alone in the .dbml and the designer.vb and use the Linq conversion of Varchar(1) to CHAR(1) and deal with the differences in your code.
2. Re-design your database to use CHAR(1) types instead of nVarChar(1) and ensure there is exactly one character in the data field in the table in question. Then delete and re-add the table to the .dbml file. If you need to have a one character field, then your database should have been designed with a CHAR(1) database type initially. This is the PREFERRED solution if you need to have a CHAR(1). If there are foreign constraints on this field, you will have to delete them and re-build them. If there are indexes on this field you may have to delete and re-build them as well.
3. Re-design your database and convert nVarChar(1) to nVarChar(2). Then delete and re-add the table to the .dbml file. This is NOT the PREFERRED solution becuase future development or code refactoring will have to "downsize" this field at a later date. Since "downsizing" is much much harder to do than "upsizing", don't implement this solution unless you are NOT going to be the future developer. If there are foreign constraints on this field, you will have to delete them and re-build them. If there are indexes on this field you may have to delete and re-build them as well.