How To Insert Records Into A Table That Has No Primary Key, using LINQ to SQL?

Introduction

I have found that when it comes to insert operation using LINQ in a table which has no primary key defined, the answer to this question on Google comes as Writing a Stored Procedure and Importing that Stored Procedure. Although there is an alternative to this approach.

The alternative is as simple as editing the .EDMX in the XML Text Editor.

Background

The background needed is a prior knowledge on the LINQ (Syntax, etc.). C#, exporting functions and stored procedure from EDMX.

Using the Code

In my example, I made an EDMX which I imported from database and which looks something like this. The name of EDMX is tbl_SalesPeopleInfo.edmx.

After I have created the EDMX, I would edit the EDMX in an XML Editor. Right click on the EDMX and select the Open With --> Select XML (Text) Editor.

The editor would open your EDMX in XML Text Editor, which would look something similar to this, depending on the number of tables you have and the relating columns. To keep it simple, I have just one table and the table has no primary key column. The LINQ would not allow me to update existing values in my table by conventional means, however there is a workaround about this issue. However it should be kept in mind that it could create consequences for the implementor as let's say the implementor wants to update just one row but it might so happen that multiple rows would get updated. How?

I would explain in the below lines.

The EDMX In XML Editor would look something like this (I am covering only the SSDL section):

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" 
xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="Scratchpad_BusDevModel1.Store" 
      Alias="Self" Provider="System.Data.SqlClient" 
      ProviderManifestToken="2008" 
      xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" 
      xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
        <EntityContainer Name="Scratchpad_BusDevModel1StoreContainer">
          <EntitySet Name="tbl_d_sales_people_info" 
          EntityType="Scratchpad_BusDevModel1.Store.tbl_d_sales_people_info" 
          store:Type="Tables" store:Schema="dbo" 
          store:Name="tbl_d_sales_people_info">
            <DefiningQuery>SELECT 
      [tbl_d_sales_people_info].[PKID] AS [PKID], 
      [tbl_d_sales_people_info].[Name] AS [Name], 
      [tbl_d_sales_people_info].[Surname] AS [Surname], 
      [tbl_d_sales_people_info].[FirstName] AS [FirstName], 
      [tbl_d_sales_people_info].[RM_SLS_DM] AS [RM_SLS_DM], 
      [tbl_d_sales_people_info].[Branch] AS [Branch], 
      [tbl_d_sales_people_info].[Branch_No] AS [Branch_No], 
      [tbl_d_sales_people_info].[Channel] AS [Channel], 
      [tbl_d_sales_people_info].[Telephone] AS [Telephone], 
      [tbl_d_sales_people_info].[CellPhone] AS [CellPhone], 
      [tbl_d_sales_people_info].[FaxNumber] AS [FaxNumber], 
      [tbl_d_sales_people_info].[RSM_ID] AS [RSM_ID], 
      [tbl_d_sales_people_info].[ManCode] AS [ManCode], 
      [tbl_d_sales_people_info].[CISOfficerCode] AS [CISOfficerCode], 
      [tbl_d_sales_people_info].[GroupNumberRangeStart] AS [GroupNumberRangeStart], 
      [tbl_d_sales_people_info].[GroupNumberRangeEnd] AS [GroupNumberRangeEnd], 
      [tbl_d_sales_people_info].[NonResGroupRangeStart] AS [NonResGroupRangeStart], 
      [tbl_d_sales_people_info].[NonResGroupRangeEnd] AS [NonResGroupRangeEnd], 
      [tbl_d_sales_people_info].[Notes] AS [Notes], 
      [tbl_d_sales_people_info].[NT_Modify_User] AS [NT_Modify_User], 
      [tbl_d_sales_people_info].[Modify_Dt] AS [Modify_Dt], 
      [tbl_d_sales_people_info].[Email] AS [Email], 
      [tbl_d_sales_people_info].[Assistant1_Email] AS [Assistant1_Email], 
      [tbl_d_sales_people_info].[Assistant2_Email] AS [Assistant2_Email], 
      [tbl_d_sales_people_info].[Manager] AS [Manager], 
      [tbl_d_sales_people_info].[F_No] AS [F_No], 
      [tbl_d_sales_people_info].[PHX_Brn_No] AS [PHX_Brn_No], 
      [tbl_d_sales_people_info].[Employee_No] AS [Employee_No], 
      [tbl_d_sales_people_info].[Elvis] AS [Elvis], 
      [tbl_d_sales_people_info].[Status] AS [Status]
      FROM [dbo].[tbl_d_sales_people_info] AS [tbl_d_sales_people_info]</DefiningQuery>
          </EntitySet>
        </EntityContainer>

Now what you need to do is define a function just inside the SSDL section which would update the table.

The function I have written just after <schema Namespace and before <Entity Container Name.

LINQ provides functionality to implement function inside SSDL. How? Under The SSDL Section type <function>, the intellisense would give you proper hint.

  <Function Name="InsertUpdatedSalesPeopleData" 
IsComposable="false">
              <CommandText>
                  UPDATE tbl_d_sales_people_info SET Name=@Name ,Surname=@Surname ,
                  FirstName=@FirstName,RM_SLS_DM=@RM_SLS_DM ,Branch=@Branch ,
                  Branch_No=@Branch_No ,Channel=@Channel ,Telephone=@Telephone ,
                  CellPhone=@CellPhone ,FaxNumber=@FaxNumber ,RSM_ID=@RSM_ID ,
                  ManCode=@ManCode ,CISOfficerCode=@CISOfficerCode ,
                  GroupNumberRangeStart=@GroupNumberRangeStart ,
                  GroupNumberRangeEnd=@GroupNumberRangeEnd  ,
                  NonResGroupRangeStart=@NonResGroupRangeStart ,
                  NonResGroupRangeEnd=@NonResGroupRangeEnd  ,Notes=@Notes  ,
                  NT_Modify_User=@NT_Modify_User ,Modify_Dt=@Modify_Dt ,
                  Email=@Email ,Assistant1_Email=@Assistant1_Email ,
                  Assistant2_Email=@Assistant2_Email ,Manager=@Manager ,
                  F_No=@F_No ,PHX_Brn_No=@PHX_Brn_No ,Employee_No=@Employee_No ,
                  Status=@Status  WHERE PKID=@PKID             
              
              </CommandText>

After defining this, we need to define all the parameters which are used, note parameter defined should be of the correct type (i.e., as defined in the database schema) and we define parameter just after:

</CommandText>

and after all parameters have been defined, we close the:

</Function>

The parameters are defined as below:

<Parameter Name="Name" Type="varchar" Mode="In"/>
             <Parameter Name="Surname" Type="varchar" Mode="In"/>
             <Parameter Name="FirstName" Type="varchar" Mode="In"/>
             <Parameter Name="RM_SLS_DM" Type="varchar" Mode="In"/>
             <Parameter Name="Branch" Type="varchar" Mode="In"/>
             <Parameter Name="Branch_No" Type="varchar" Mode="In"/>
             <Parameter Name="Channel" Type="varchar" Mode="In"/>
             <Parameter Name="Telephone" Type="varchar" Mode="In"/>
             <Parameter Name="CellPhone" Type="varchar" Mode="In"/>
             <Parameter Name="FaxNumber" Type="varchar" Mode="In"/>
             <Parameter Name="RSM_ID" Type="int" Mode="In"/>
             <Parameter Name="ManCode" Type="varchar" Mode="In"/>
             <Parameter Name="CISOfficerCode" Type="varchar" Mode="In"/>
             <Parameter Name="GroupNumberRangeStart" Type="smallint" Mode="In"/>
             <Parameter Name="GroupNumberRangeEnd" Type="smallint" Mode="In"/>
             <Parameter Name="NonResGroupRangeStart" Type="smallint" Mode="In"/>
             <Parameter Name="NonResGroupRangeEnd" Type="smallint" Mode="In"/>
             <Parameter Name="Notes" Type="varchar" Mode="In"/>
             <Parameter Name="NT_Modify_User" Type="varchar" Mode="In"/>
             <Parameter Name="Modify_Dt" Type="datetime" Mode="In"/>
             <Parameter Name="Email" Type="varchar" Mode="In"/>
             <Parameter Name="Assistant1_Email" Type="varchar" Mode="In"/>
             <Parameter Name="Assistant2_Email" Type="varchar" Mode="In"/>
             <Parameter Name="Manager" Type="varchar" Mode="In"/>
             <Parameter Name="F_No" Type="varchar" Mode="In"/>
             <Parameter Name="PHX_Brn_No" Type="int" Mode="In"/>
             <Parameter Name="Employee_No" Type="varchar" Mode="In"/>
             <!--<Parameter Name="Elvis" Type="bit" Mode="In"/>-->
             <Parameter Name="Status" Type="varchar" Mode="In"/>
             <Parameter Name="PKID" Type="bigint" Mode="In"/>

After doing this, save and close the editor. Now, we need to import this function and use it in our code behind.

In the Model Browser.Add function import, as this function is not returning any value, we should select the return value as Null.

Now the function can be used in the code behind like this:

And it's done. Now this update function would update the table.

Points of Interest

Note: In my design of the Table, PKID is something which is unique and it has no duplicate, although PKID is not a primary key but it has all unique values..

History

• 12^th December, 2014: Initial version