Translating Insert into select to Entity Framework 6

c# entity-framework entity-framework-6 sql sql-server

Question

Multiple consecutive number series should be created. The serial number would appear as, for instance.

M1-00000001 M2-00000001 M1-00000002 M3-00000001 M2-00000002 ...

CREATE TABLE [dbo].[SerialNumber](
    [Id] [int] NOT NULL,
    [MachineId] [nchar](10) NOT NULL,
    [Snr] [int] NOT NULL,
 CONSTRAINT [PK_SerialNumber] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]

A new serial number is then generated using this query.

DECLARE @machine_id AS NCHAR(10)
SET @machine_id = 'M2'
INSERT INTO SerialNumber (MachineId,Snr)

SELECT @machine_id ,IIF(MAX(Snr) is not null,MAX(SNR)+1,1)
from SerialNumber
where MachineId=@machine_id

In Entity Framework 6.1.3, how would I go about doing this?

EDIT: I completely revised the question; I'm not sure whether it was the right move, but someone will surely let me know if it was inappropriate.

1
1
12/19/2016 3:15:34 PM

Accepted Answer

Edited following feedback: This calls forDbContext Must be equipped with a suitableSerialNumbers serial-number table representation attribute. TheSerialNumber the class that represents objects is as follows:

public class SerialNumber
{
    public int Id {get;set;} //Added after edit
    public string MachineId {get;set;}
    public int Snr {get;set;} //I assume it is a nullable int
}

then some code like this ought to do the job:

public void AddSerial(string machineId)
{
    using (var context = new MyDbContext())
    {
        int max = context.SerialNumbers
            .Where(sn => sn.MachineId  == machineId)
            .Select(sn => sn.Snr)
            .DefaultIfEmpty(0)
            .Max(); //Extra clauses added after edit
        max++;
        context.SerialNumbers.Add(new SerialNumber{ MachineId = machineId, Snr = max});
        context.SaveChanges();
    }
}
2
12/19/2016 12:24:02 PM

Popular Answer

You must construct an object and attach it to your database since Entity Framework is an ORM, as seen below:

You have to

  • a class with the nameSerialNumber
  • a map with a database table below
  • Maintain your item in aDBContext

You may get a user's manual for Entity Framework at Here.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow