Write SQL queries using LINQ from linq object List

Question

I have a linq object and I want to write the query using linq.

please help me.

INPUT:

var tags = (from row in tempChildData.AsEnumerable()
                                join tagOrder in tupleInfoDataset.Tables["TagHierarchy"].AsEnumerable() on row.Field<Int64>("TAGID") equals tagOrder.Field<Int64>("TAGID")
                                join tagName in tupleInfoDataset.Tables["SequenceChoiceList"].AsEnumerable() on tagOrder.Field<Int64>("PARENTTAGID") equals tagName.Field<Int64>("TAGID")
                                join facet in tupleInfoDataset.Tables["FacetType"].AsEnumerable() on tagName.Field<string>("Tag_Name") equals facet.Field<string>("Facetname")
                                join tagIdInfo in schDataTogetTagid.AsEnumerable() on row.Field<string>("refTagName").Contains(":") ? row.Field<string>("refTagName").Split(':').Last():row.Field<string>("refTagName") equals tagIdInfo.Field<string>("TAGNAME")
                               where ( childList.Contains(row.Field<Int64>("TAGID")) && facet.Field<string>("FacetType").ToLower().Equals("ctype"))
                               select new
                               {
                                   Tagid = row.Field<Int64>("TAGID"),
                                   TagIdToInsert=tagIdInfo.Field<Int64>("TAGID"),
                                   MaxOccur = row.Field<string>("Maxoccurs"),
                                   MinOccur =Convert.ToInt32(Convert.ToString(row.Field<string>("Minoccur"))),
                                   ParentTagId=tagOrder.Field<Int64>("PARENTTAGID"),
                                   Order=tagOrder.Field<Int64>("TAG_ORDER"),
                                   ParentTagname = tagName.Field<string>("Tag_Name"),
                                   FacetId=facet.Field<Int64>("FacetID")
                               }).ToList();


                    var parentTagID = (from tagIdInfo in tupleInfoDataset.Tables["Tuple"].AsEnumerable()
                                      where tagIdInfo.Field<Int64>("TAGID").Equals(key.Key)
                                      select tagIdInfo.Field<Int64>("ConceptID")).ToList();
                    long parentID =Convert.ToInt64(parentTagID[0]);

Now i want the query out of the above code as:

INSERT INTO TUPLE_MAP (TagId,ParentTagId,ParentTagname,MinOccur,MaxOccur,Order)
VALUES (TagIdToInsert,ParentTagId,ParentTagname,MinOccur,MaxOccur,Order)

Please help me I don't know how to write SQL queries using linq

Welcome to StackOverflow: if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! — marc_s
– marc_s, Commented Apr 13, 2012 at 6:10
Here i want to insert data in the table from the "tags" objject list in the given query.. i am using sql compact 3.5.. — lokendra jayaswal
– lokendra jayaswal, Commented Apr 13, 2012 at 6:56
Using the tags object i want to write the Sql compact queries to insert the data in the database;INSERT INTO TUPLE_MAP (TagId,ParentTagId,ParentTagname,MinOccur,MaxOccur,Order) VALUES (TagIdToInsert,ParentTagId,ParentTagname,MinOccur,MaxOccur,Order) where all the "tags" object data is used above — lokendra jayaswal
– lokendra jayaswal, Commented Apr 13, 2012 at 7:14

Community · Accepted Answer · 2017-05-23 10:08:50Z

1

Maybe something like this:

using(var db=new DataContext("YourConnectionStringHERE"))
{
  db.TUPLE_MAP.InsertAllOnSubmit(tags.Select (t =>
  new TUPLE_MAP()
  {
       TagId=t.TagIdToInsert,
       ParentTagId=t.ParentTagId,
       ParentTagname=t.ParentTagname,
       MinOccur=t.MinOccur,
       MaxOccur=t.MaxOccur,
       Order=t.Order
  }));
  db.SubmitChanges();
}

Or if you want to use the parentID then something like this:

using(var db=new DataContext("YourConnectionStringHERE"))
{
  db.TUPLE_MAP.InsertAllOnSubmit(tags.Select (t =>
  new TUPLE_MAP()
  {
       TagId=t.TagIdToInsert,
       ParentTagId=parentID,
       ParentTagname=t.ParentTagname,
       MinOccur=t.MinOccur,
       MaxOccur=t.MaxOccur,
       Order=t.Order
  }));
  db.SubmitChanges();
}

where db is your linq data context

Useful references:

How to: Insert Rows Into the Database (LINQ to SQL)

EDIT

So if you are using the Compact database 3.5 then many something like this:

using (var conn =new SqlCeConnection("Data Source = test.sdf; Password ='pass'"))
{
        foreach (var tag in tags)
        {
            using(var cmd = conn.CreateCommand())
            {

                cmd.CommandText = @"INSERT INTO TUPLE_MAP (TagId,ParentTagId,ParentTagname,MinOccur,MaxOccur,Order)
                                    VALUES (@TagIdToInsert,@ParentTagId,@ParentTagname,@MinOccur,@MaxOccur,@Order)";

                cmd.Parameters.AddWithValue("@TagIdToInsert", tag.TagIdToInsert);
                cmd.Parameters.AddWithValue("@ParentTagId", tag.ParentTagId);
                cmd.Parameters.AddWithValue("@ParentTagname", tag.ParentTagname);
                cmd.Parameters.AddWithValue("@MinOccur", tag.MinOccur);
                cmd.Parameters.AddWithValue("@MaxOccur", tag.MaxOccur);
                cmd.Parameters.AddWithValue("@Order", tag.Order);

                cmd.ExecuteNonQuery();

            }
        }
    }

Useful references:

edited May 23, 2017 at 10:08

CommunityBot

11 silver badge

answered Apr 13, 2012 at 6:42

Arion

31.3k10 gold badges74 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

lokendra jayaswal Over a year ago

I want to use the query to insert data in Compact database 3.5 so Datacontext can't be use ..please reply thanx in advance..

Arion Over a year ago

Updated the answer take a look

lokendra jayaswal Over a year ago

There was an error parsing the query. [ Token line number = 1,Token line offset = 13,Token in error = 24 ] im getting this error i tried but could not be resolved

lokendra jayaswal Over a year ago

Since there is Order column in my query which it is throwing error..How to handle that..please reply..im stuck here

lokendra jayaswal Over a year ago

Thanx alot its working..There was mistake from my end..I am extremely sorry for the inconvenience...Thanx a ton Sir..

|

userGS · Accepted Answer · 2012-04-13 06:15:33Z

0

Use linq Pad or sql profiler to see the generated SQL. You can also use visual studio for that purpose. In the debug mode,hold cursor on the variable "tags", you will be able to see the SQL.

answered Apr 13, 2012 at 6:15

userGS

2201 silver badge7 bronze badges

1 Comment

Arion Over a year ago

This do not address the OPs question. He is not asking how to get the sql of the already written linq code. He is asking how to insert using linq

Pravin Pawar · Accepted Answer · 2012-04-13 06:39:33Z

0

I am assuming you are using Linq to SQL, if you are doing so you would have entity called Tuple_map in you xxxDataContext. Then you would just have to create object of that entity something like this....

using (XXXDataContext context = new XXXDataContext())
{
   Tuple_map obj = new Tuple_map();
   //Populate obj properties like obj.tabid = from objects you got it from above query
   context.Tuple_map.InsertOnSubmit(obj);
   context.SubmitChanges();
}

answered Apr 13, 2012 at 6:39

Pravin Pawar

2,5793 gold badges34 silver badges40 bronze badges

1 Comment

lokendra jayaswal Over a year ago

I want to make queries for sql compact 3.5 .. the above code does not work..please reply

Collectives™ on Stack Overflow

Write SQL queries using LINQ from linq object List

3 Answers 3

6 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related