Update value if exist or insert in SQL with C# linq

Question

I have this code which is working fine and I update a value if exists or insert if not

List<KodikosProtimisis> newList = new List<KodikosProtimisis>();
newList.Add(new KodikosProtimisis { Kodikos = "100", Perigrafi = "Description", TaricTiposMetrou = "103" });
newList.Add(new KodikosProtimisis { Kodikos = "110", Perigrafi = "Description", TaricTiposMetrou = "112" });
newList.Add(new KodikosProtimisis { Kodikos = "420", Perigrafi = "Description", TaricTiposMetrou = "112" });
.
.
.
(more values)

string connectionString = Configuration.Parameters.Config.ConnectionString;

string query = @"IF EXISTS(SELECT * FROM dbo.KodikosProtimisis WHERE Kodikos = @kodikos and DiasafistisId=@diasafistisId)
                        UPDATE dbo.KodikosProtimisis 
                        SET Kodikos = @kodikos, DiasafistisId=@diasafistisId,Perigrafi=@perigrafi,TaricTiposMetrou = @taricTiposMetrou
                        WHERE Kodikos = @kodikos and DiasafistisId=@diasafistisId or DiasafistisId is null
                    ELSE
                        INSERT INTO dbo.KodikosProtimisis(Kodikos, Perigrafi,TaricTiposMetrou, DiasafistisId) VALUES(@kodikos, @perigrafi,@taricTiposMetrou, @diasafistisId);";

foreach (var person in newList)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand(query, conn))
    {
        //δίνουμε τις παραμέτρους στο ερώτημα μας 
        cmd.Parameters.Add("@kodikos", SqlDbType.NVarChar, 100).Value = person.Kodikos;
        cmd.Parameters.Add("@perigrafi", SqlDbType.NVarChar, 200).Value = person.Perigrafi;
        cmd.Parameters.Add("@diasafistisId", SqlDbType.Int, 100).Value = DefaultDiasafistis.DiasafistisDefault.Id;
        cmd.Parameters.Add("@taricTiposMetrou", SqlDbType.NVarChar, 150).Value = person.TaricTiposMetrou;

        conn.Open();
        int rowsAffected = cmd.ExecuteNonQuery();
        conn.Close();
    }
}

With this code I manage to do the work with only one query to the SQL, but I was wondering if there is an easier or more elegant way to do it.

For example if I could use linq and lambda expressions (which I don't have much experience with).

I want (if it's possible) to remove the query and put something that I can control more easy and most important to maintain it easily if something changes, for example database design or massively change something in the existing values.

EDIT: I really don't understand why I have the negative votes. I give a specific code that already works and I ask if there is a better way.

if you want to write with LINQ you have to use [EF](https://learn.microsoft.com/en-us/ef/) or any other [ORM](https://en.wikipedia.org/wiki/Object-relational_mapping) — styx, Mar 22 '20 at 15:47
Your SQL script still can be rewritten in terms of `MERGE` operator, [more info](https://en.wikipedia.org/wiki/Merge_(SQL)) — Alexey S. Larionov, Mar 22 '20 at 15:49
@AlexLarionov yes but I already wrote I want to avoid using simple sql queries — rippergr, Mar 22 '20 at 15:53
@styx I already have linq queries in my program , but I don't know if and how can I rewrite this using linq — rippergr, Mar 22 '20 at 15:55
@rippergr just break down to small queries, I.E "IF EXISTS.." can be replaced with `Any()` — styx, Mar 22 '20 at 15:57
[There's a better way to do it in TSQL...](https://stackoverflow.com/a/52780490/3094533) — Zohar Peled, Mar 22 '20 at 16:18
@ZoharPeled What is the point to change an already working code with another. The link you gave me just shows another way to execute a query. There is no error handling as they mention. — rippergr, Mar 22 '20 at 18:27
@rippergr What ZoharPeled is suggesting you is to wrap the whole thing in an atomic transaction that is not something you are doing in your code. — gwt, Mar 22 '20 at 18:57
Because it's a better code, both from the thread safty perspective and from the perfomance perspective. — Zohar Peled, Mar 22 '20 at 19:07

score 0 · Answer 1 · edited Mar 22 '20 at 17:20

0

By using Entity Framework, you can use AddOrUpdate method.

You need include System.Data.Entity.Migrations; in your using statements.

But please note that it is not atomic and it is not threadsafe.

edited Mar 22 '20 at 17:20

marc_s

732,580
175
1,330
1,459

answered Mar 22 '20 at 16:30

gwt

2,331
4
37
59

Sorry but I think this is worse than the one I use :) – rippergr Mar 22 '20 at 18:22
@rippergr There is no reason for you to be sorry :) I don't know what you mean by worse?! Based on what you asked: "if there is an easier or more elegant way....something that I can control more easy and most important to maintain it easily if something changes" and since this answer suggests to use linqtoentities along with strongly typed entities, it definitely provides an easier and more elegant way to do what you want and it absolutely makes change management easier! Since you do not need to use non-strongly typed dirty strings to generate TSQL anymore. – gwt Mar 22 '20 at 19:02
@Karamafrooz I didn't mean worse I meant more complicated. – rippergr Mar 23 '20 at 12:45
@rippergr I can't see how is calling a single method that abstracts away what you are struggling to do in your question or in your posted answer more complicated? – gwt Mar 23 '20 at 12:59
@I believe I have more control on debug with this code. Maybe I am wrong – rippergr Mar 23 '20 at 13:32

rippergr · Accepted Answer · 2020-03-23T09:49:19.293

I found the answer. Here is the code that uses linq and lambda expressions

        List<KodikosProtimisis> newListKodikosProtimisis = new List<KodikosProtimisis>();
                    newListKodikosProtimisis.Add(new KodikosProtimisis { Kodikos = "100", Perigrafi = "Δασμός τρίτων χωρών έναντι όλων (erga omnes)", TaricTiposMetrou = "103" });
                    newListKodikosProtimisis.Add(new KodikosProtimisis { Kodikos = "110", Perigrafi = "Αυτόνομη δασμολογική αναστολή έναντι όλων (erga omnes) (προσωρινή αναστολή των αυτόνομων δασμών για ορισμένα εμπορεύματα του αγροτικού, χημικού, αεροναυπηγικού και του ηλεκτρονικού τομέα)", TaricTiposMetrou = "112" });
                    newListKodikosProtimisis.Add(new KodikosProtimisis { Kodikos = "420", Perigrafi = "Ποσόστωση τελωνειακής ένωσης", TaricTiposMetrou = "112" });
                    newListKodikosProtimisis.Add(new KodikosProtimisis { Kodikos = "422", Perigrafi = "Ποσόστωση τελωνειακής ένωσης", TaricTiposMetrou = "112" });
    .
    .
    .
    more values
                    foreach (var kodikosProtimisis in newListKodikosProtimisis)
                    {

                        using (Ektel db = new Ektel(EktelDataContextManager.ConnectionString))
                        {

                            var qry = db.KodikosProtimisis.Where(x => x.Kodikos == kodikosProtimisis.Kodikos).FirstOrDefault();

                            if (qry != null)
                            {
                                // The value exists .Update
                                qry.Kodikos = kodikosProtimisis.Kodikos;
                                qry.Perigrafi = kodikosProtimisis.Perigrafi;
                                qry.TaricTiposMetrou = kodikosProtimisis.TaricTiposMetrou;
                            }
                            else
                            {
                                //The value doesnt exist.Insert.
                                db.KodikosProtimisis.InsertOnSubmit(new KodikosProtimisis
                                {
                                    Kodikos = kodikosProtimisis.Kodikos,
                                    Perigrafi = kodikosProtimisis.Perigrafi,
                                    TaricTiposMetrou = kodikosProtimisis.TaricTiposMetrou
                                });
                            }

                        db.SubmitChanges();
                    }

                }

Update value if exist or insert in SQL with C# linq

2 Answers2