1

I am having some problem while trying to create a record on DB. I am using Entity Framework and SQL Server.

I have done some research but could not find anything that helped me. I saw many people mentioning about primary key or mapping on .edmx file. I have changed my .edmx file according to answers that I have found, trying to remove some keys and leaving only one as well as changing store:Type to Table rather than View. The primary key is being shown on .edmx but I am not setting it as I suppose it's auto-generated.

Below part of the .edmx

<!--Errors Found During Generation:
warning 6002: The table/view 'xxx.dbo.FilteredNew_timesheetlineitem' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.-->
        <EntityType Name="FilteredNew_timesheetlineitem">
          <Key>
            <PropertyRef Name="createdbydsc" />
            <PropertyRef Name="modifiedbydsc" />
            <PropertyRef Name="new_accounttimesheetlineitemiddsc" />
            <PropertyRef Name="new_approvedbyuseriddsc" />
            <PropertyRef Name="new_billratetimesheetlineitemiddsc" />
            <PropertyRef Name="new_billtoiddsc" />
            <PropertyRef Name="new_candidatetimesheetlineitemiddsc" />
            <PropertyRef Name="new_contracttypetimesheetlineitemiddsc" />
            <PropertyRef Name="new_paytypetimesheetlineitemiddsc" />
            <PropertyRef Name="new_slatimesheetlineitemiddsc" />
            <PropertyRef Name="new_stream3timesheetlineitemiddsc" />
            <PropertyRef Name="new_timesheetlineitemid" />
            <PropertyRef Name="new_timesheettimesheetlineitemiddsc" />
            <PropertyRef Name="ownerid" />
            <PropertyRef Name="owneriddsc" />
            <PropertyRef Name="statecode" />
            <PropertyRef Name="transactioncurrencyiddsc" />
          </Key>
          <Property Name="createdby" Type="uniqueidentifier" />
          <Property Name="createdbydsc" Type="int" Nullable="false" />
          <Property Name="createdbyname" Type="nvarchar" MaxLength="160" />
          <Property Name="createdbyyominame" Type="nvarchar" MaxLength="160" />
          <Property Name="createdon" Type="datetime" />
          <Property Name="createdonutc" Type="datetime" />
          <Property Name="createdonbehalfby" Type="uniqueidentifier" />
          <Property Name="createdonbehalfbyname" Type="nvarchar" MaxLength="160" />
          <Property Name="createdonbehalfbyyominame" Type="nvarchar" MaxLength="160" />
          <Property Name="exchangerate" Type="decimal" Precision="23" Scale="10" />
          <Property Name="importsequencenumber" Type="int" />
          <Property Name="modifiedby" Type="uniqueidentifier" />
          <Property Name="modifiedbydsc" Type="int" Nullable="false" />
          <Property Name="modifiedbyname" Type="nvarchar" MaxLength="160" />
          <Property Name="modifiedbyyominame" Type="nvarchar" MaxLength="160" />
          <Property Name="modifiedon" Type="datetime" />
          <Property Name="modifiedonutc" Type="datetime" />
          <Property Name="modifiedonbehalfby" Type="uniqueidentifier" />
          <Property Name="modifiedonbehalfbyname" Type="nvarchar" MaxLength="160" />
          <Property Name="modifiedonbehalfbyyominame" Type="nvarchar" MaxLength="160" />
          <Property Name="new_accounttimesheetlineitemid" Type="uniqueidentifier" />
          <Property Name="new_accounttimesheetlineitemiddsc" Type="int" Nullable="false" />
          <Property Name="new_accounttimesheetlineitemidname" Type="nvarchar" MaxLength="160" />
          <Property Name="new_accounttimesheetlineitemidyominame" Type="nvarchar" MaxLength="160" />
          <Property Name="new_approved" Type="bit" />
          <Property Name="new_approvedbyuserid" Type="uniqueidentifier" />
          <Property Name="new_approvedbyuseriddsc" Type="int" Nullable="false" />
          <Property Name="new_approvedbyuseridname" Type="nvarchar" MaxLength="160" />
          <Property Name="new_approvedbyuseridyominame" Type="nvarchar" MaxLength="160" />
          <Property Name="new_approveddatetime" Type="datetime" />
          <Property Name="new_approveddatetimeutc" Type="datetime" />
          <Property Name="new_approvedname" Type="nvarchar" MaxLength="4000" />
          <Property Name="new_billrate" Type="money" />
          <Property Name="new_billratetimesheetlineitemid" Type="uniqueidentifier" />
          <Property Name="new_billratetimesheetlineitemiddsc" Type="int" Nullable="false" />
          <Property Name="new_billratetimesheetlineitemidname" Type="nvarchar" MaxLength="100" />
          <Property Name="new_billrate_base" Type="money" />
          <Property Name="new_billtoid" Type="uniqueidentifier" />
          <Property Name="new_billtoiddsc" Type="int" Nullable="false" />
          <Property Name="new_billtoidname" Type="nvarchar" MaxLength="160" />
          <Property Name="new_billtoidyominame" Type="nvarchar" MaxLength="160" />
          <Property Name="new_candidatetimesheetlineitemid" Type="uniqueidentifier" />
          <Property Name="new_candidatetimesheetlineitemiddsc" Type="int" Nullable="false" />
          <Property Name="new_candidatetimesheetlineitemidname" Type="nvarchar" MaxLength="100" />
          <Property Name="new_contracttypetimesheetlineitemid" Type="uniqueidentifier" />
          <Property Name="new_contracttypetimesheetlineitemiddsc" Type="int" Nullable="false" />
          <Property Name="new_contracttypetimesheetlineitemidname" Type="nvarchar" MaxLength="100" />
          <Property Name="new_enddatetime" Type="datetime" />
          <Property Name="new_enddatetimeutc" Type="datetime" />
          <Property Name="new_firstname" Type="nvarchar" MaxLength="100" />
          <Property Name="new_hours" Type="decimal" Precision="23" Scale="10" />
          <Property Name="new_jobseekerid" Type="nvarchar" MaxLength="100" />
          <Property Name="new_lastname" Type="nvarchar" MaxLength="100" />
          <Property Name="new_lunchtime" Type="int" />
          <Property Name="new_lunchtimename" Type="nvarchar" MaxLength="4000" />
          <Property Name="new_name" Type="nvarchar" MaxLength="100" />
          <Property Name="new_payrollreference" Type="nvarchar" MaxLength="100" />
          <Property Name="new_paytypetimesheetlineitemid" Type="uniqueidentifier" />
          <Property Name="new_paytypetimesheetlineitemiddsc" Type="int" Nullable="false" />
          <Property Name="new_paytypetimesheetlineitemidname" Type="nvarchar" MaxLength="100" />
          <Property Name="new_slatimesheetlineitemid" Type="uniqueidentifier" />
          <Property Name="new_slatimesheetlineitemiddsc" Type="int" Nullable="false" />
          <Property Name="new_slatimesheetlineitemidname" Type="nvarchar" MaxLength="100" />
          <Property Name="new_startdatetime" Type="datetime" />
          <Property Name="new_startdatetimeutc" Type="datetime" />...

Below method that saves data from ViewModel

public void SaveTimesheetLine(TimesheetViewModel timesheetLineVm)
    {
        string payrollId = Convert.ToString(Session["payroll"]);
        using (MSCRMEntities t = new MSCRMEntities())
        {
            var line = new FilteredNew_timesheetlineitem
            {
                new_billtoid = timesheetLineVm.TimesheetLineViewModels.BillToId,
                new_timesheettimesheetlineitemid = timesheetLineVm.TimesheetId,
                new_slatimesheetlineitemid = timesheetLineVm.TimesheetLineViewModels.SlaId,
                new_billratetimesheetlineitemid = timesheetLineVm.TimesheetLineViewModels.BillRateId,
                new_stream3timesheetlineitemid = timesheetLineVm.TimesheetLineViewModels.Stream3Id,
                new_contracttypetimesheetlineitemid = timesheetLineVm.TimesheetLineViewModels.ContractTypeId,
                new_firstname = timesheetLineVm.TimesheetLineViewModels.EmployeeFirstName,
                new_lastname = timesheetLineVm.TimesheetLineViewModels.EmployeeLastName,
                new_accounttimesheetlineitemid = timesheetLineVm.TimesheetLineViewModels.EmployerId,
                new_payrollreference = payrollId,
                new_timesheetdate = timesheetLineVm.TimesheetDate,
                new_candidatetimesheetlineitemid = timesheetLineVm.TimesheetLineViewModels.CandidateId,
                // taken from user input
                new_startdatetime = timesheetLineVm.TimesheetLineViewModels.StartDate,
                new_enddatetime = timesheetLineVm.TimesheetLineViewModels.EndDate,
                new_paytypetimesheetlineitemidname = Convert.ToString(timesheetLineVm.TimesheetLineViewModels.PayType),
                new_lunchtime = 2,
                new_submittedhours = timesheetLineVm.TimesheetLineViewModels.SubmittedHours
            };

            t.FilteredNew_timesheetlineitem.Add(line);
            // t.Entry(line).State = (EntityState) Microsoft.Xrm.Sdk.EntityState.Created;

            t.SaveChanges();
        }
    }

The error I am getting is:

Unable to update the EntitySet because it has a definingQuery and no Insert Fnction element exists in the modificationFunctionMapping element to support the current operation.

Would anyone know why such an error is showing up?

Thanks in advance, Dem

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
dem93
  • 31
  • 2
  • 7
  • Possible duplicate of [Unable to update the EntitySet - because it has a DefiningQuery and no element exist](https://stackoverflow.com/questions/7583770/unable-to-update-the-entityset-because-it-has-a-definingquery-and-no-updatefu) – Michael Freidgeim Sep 21 '18 at 08:08

2 Answers2

1

Apparently, 'xxx.dbo.FilteredNew_timesheetlineitem' does not have a primary key.

Ricardo Peres
  • 13,724
  • 5
  • 57
  • 74
  • Hi Ricardo, It actually has a PK. I have double checked the DB and its there! Don't know why that is happening. – dem93 Dec 01 '14 at 05:33
  • I have removed most keys and left only the ones I am being able to retrieve from viewModel. I have also changed the store:Schema to Schema in the entityset config as well as removing the definingQuery. I am now receiving the following error: Update or insert of view or function failed because it contains a derived or constant field. Any idea of what it could be? – dem93 Dec 01 '14 at 06:03
  • What about the message "table/view 'xxx.dbo.FilteredNew_timesheetlineitem' does not have a primary key defined"? – Ricardo Peres Dec 01 '14 at 10:13
  • Hey Ricardo, It is weird. I don't really know why that message is there. Whenever I open the database through SQL Management Studio I can see a PK. Also, It's set as GUID (MSDynamics purpose) and has the Identity field. I have gone through few books and haven't found any answer yet. Would you know why such message is coming up? Would there be any chance of the wizard for adding new EDM is not being able to find PK? Regards, Dem – dem93 Dec 02 '14 at 02:31
  • Also, I noticed that I am not able to create tables with the edm wizard, only views. Would that be the reason for not being able to insert new data? I have just checked the view and there is no PK lol – dem93 Dec 02 '14 at 06:09
1

Dynamics CRM DB can't be updated directly, you need to use CRM Web Services instead of SQL queries or EntityFramework.

Guido Preite
  • 14,905
  • 4
  • 36
  • 65
  • Hi Guido, I have realized that. At first I thought I was using EF behind the scenes but it turns out I should use CRM Service. I have done few tests and I am now able to save some entities. I am having troubles with saving related entities. As in my case, I have a Timesheet entity that is 0-* to TimesheetLine. When I try to save an timesheetline, it won't save. I think that's because I can't save a timesheetline directly. So now I am trying to retrieve a timesheet and add a list of timesheetlines to it, then update. The error I am getting now is concerning the Id. How do I create that? – dem93 Dec 12 '14 at 02:44