CODEWITHSUNDEEP
c#sqlsql-serverentity-frameworkentity-framework-6
Andis59
Andis59

Reputation: 569

Translating Insert into select to Entity Framework 6

I want to create multiple sequential number series The serial number would look like - e.g.

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]

I then use this query to create a new serial number

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

How would I do this in Entity Framework 6.1.3?

EDIT: I rewrote the whole question, don't know it you are suppose to do that, but someone will probably tell me if it's considered bad...

Upvotes: 1

Views: 1676

Answers (3)

ryatkins
ryatkins

Reputation: 348

I was getting the following error: The cast to value type 'Int32' failed because the materialized value is null

I changed GDS' code to the following to handle when the select finds 0 rows and returns a null value.

public void AddSerial(string machineId)
{
    using (var context = new MyDbContext())
    {
        int max = context.SerialNumbers
            .Where(sn => sn.MachineId  == machineId)
            .Max(sn =>(int?)sn.Snr) ?? 0; //Extra clauses added after edit
        max++;
        context.SerialNumbers.Add(new SerialNumber{ MachineId = machineId, Snr = max});
        context.SaveChanges();
    }
}

Upvotes: 0

GDS
GDS

Reputation: 1387

Edited after comments: This requires DbContext to be properly set up with a SerialNumbers property that represents the SerialNumber table. The SerialNumber objects are represented by the following class: 

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
}

and then some code similar to this should 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();
    }
}

Upvotes: 2

Joe Taras
Joe Taras

Reputation: 15389

Entity Framework in an ORM, so you must create an object and link to your database, as follow:

You must:

  • have a class named SerialNumber
  • map with underlyng database table
  • persist your object in a DBContext

Here you can find a guide to use Entity Framework.

Upvotes: 2

Related Questions