Common SQL to LINQ code

Add record
1. Add a new record (for example, add a News Record)
Public static void addnewsinfo (newsinfo entity)
{
Using (sitecmsdatacontext DB = new sitecmsdatacontext ())
{
DB. newsinfo. insertonsubmit (entity );
DB. submitchanges ();
}
}

Modify Information
1. modify a record (for example, updating News)
Public static void updatenewsinfo (newsinfo entity)
{
Using (sitecmsdatacontext DB = new sitecmsdatacontext ())
{
VaR temp = dB. newsinfo. Where (W => W. newsid = entity. newsid). singleordefault ();
If (temp! = NULL)
{
Temp. Title = entity. title;
Temp. Subtitle = entity. Subtitle;
Temp. Source = entity. source;
Temp. Author = entity. Author;
Temp. classid = entity. classid;
Temp. Summary = entity. Summary;
Temp. newsdate = entity. newsdate;
Temp. content = entity. content;

DB. submitchanges ();
}
}
}

2. Update multiple records (for example, updating the news authors of a topic in a unified manner, This method is not good. We recommend that you call the stored procedure in LINQ. You can directly drag the stored procedure to the LINQ to SQL statement and call it as a method. )
Public static void updatenewsbyclassid (INT classid, string author)
{
Using (sitecmsdatacontext DB = new sitecmsdatacontext ())
{
VaR query = dB. newsinfo. Where (W => W. classid = classid );
Foreach (VAR temp in query)
{
Temp. Author = author;
}
DB. submitchanges ();
}
}

Delete record
1. delete a record (specify a primary key) (for example, delete a news item based on the news ID)
Public static void deletenewsinfo (INT newsid)
{< br> using (sitecmsdatacontext DB = new sitecmsdatacontext ()
{< br> var temp = dB. newsinfo. where (W => W. newsid = newsid ). singleordefault ();
If (temp! = NULL)
{< br> dB. newsinfo. deleteonsubmit (temp);
dB. submitchanges ();
}< BR >}

2. Batch delete data (specify the primary key array) (for example, select the news ID in the Multi-choice box to delete News)
Public static void deletenewsinfo (INT [] newsid)
{< br> using (sitecmsdatacontext DB = new sitecmsdatacontext ()
{< br> var list = dB. newsinfo. where (W => newsid. contains (W. newsid);
dB. newsinfo. deleteallonsubmit (list. tolist ();
dB. submitchanges ();
}< BR >}

3. delete data that meets the specified conditions (none, one, or more) (for example, delete news of a category)
Public static void delnewsbyclassid (INT classid)
{
Using (sitecmsdatacontext DB = new sitecmsdatacontext ())
{
VaR query = dB. newsinfo. Where (W => W. classid = classid );
DB. newsinfo. deleteallonsubmit (query );
DB. submitchanges ();
}
}

4. To delete multiple records above, you need to first query the information to be deleted, and then submit and delete the records. Let's look at the batch deletion function of Lao Zhao extension, which makes it much easier to use.
Http://www.cnblogs.com/JeffreyZhao/archive/2008/03/05/LINQ-to-SQL-Batch-Delete-Extension.html

Obtain data
1. Obtain the details of a piece of data (specify the primary key) (for example, obtain news content based on the news ID)
Public static newsinfo getnewsinfodetail (INT newsid)
{
Using (sitecmsdatacontext DB = new sitecmsdatacontext ())
{
VaR temp = dB. newsinfo. Where (W => W. newsid = newsid). singleordefault ();
Return temp;
}
}

2. Obtain the record with the specified condition (when classid is set to 0, no conditional filtering is added) (for example, to obtain news information of a topic, it is generally used in a small quantity, multiple pages are used or the number of retrieved items is specified)
Public static list <newsinfo> getnewsinfo (INT classid)
{
Using (sitecmsdatacontext DB = new sitecmsdatacontext ())
{
VaR query = from temp in db. newsinfo
Where (classid = 0) | (temp. classid = classid ))
Orderby temp. Istop descending, temp. newsdate descending
Select temp;
Return query. tolist ();
}
}

3. obtain several records of a specified condition (generally add sorting rules) (for example, extract the latest 3 industry news Records)
Public static list <newsinfo> getimagenewsinfo (INT classid, int topnum)
{
Using (sitecmsdatacontext DB = new sitecmsdatacontext ())
{
VaR query = (from temp in db. newsinfo
Where (classid = 0) | (temp. classid = classid ))
Orderby temp. Istop descending, temp. newsdate descending
Select temp). Take (topnum );

Return query. tolist ();
}
}

4. Retrieve paging records (sorting rules are mandatory) (for example, the most common news pages, search with a dot condition, based on the title or news column)
Public static list <newsinfo> getnewsinfolist (String title, int classid, int curpage, int pagesize, out int Records)
{
Using (sitecmsdatacontext DB = new sitecmsdatacontext ())
{
Records = (from temp in db. newsinfo
Where (classid = 0) | (temp. classid = classid ))
& Sqlmethods. Like (temp. title, "%" + title + "% ")
Select temp. newsid). Count ();

Int takes = curpage * pagesize;
Int skips = (curpage-1) * pagesize;

VaR query = (from temp in db. newsinfo
Where (classid = 0) | (temp. classid = classid ))
& Sqlmethods. Like (temp. title, "%" + title + "% ")
Orderby temp. newsdate descending, temp. createdate descending
Select temp). Take (takes). Skip (skips );

Return query. tolist ();
}
}

5. Use join to process data Federation of multiple tables (for example, display news with the specified news category name and newsinfo records the category ID by default)
Enter information in only one table. The object can be specified after the list.
Public static list <newsinfo> getnewslist (string classname)
{
Using (sitecmsdatacontext DB = new sitecmsdatacontext ())
{
VaR query = from temp1 in db. newsinfo
Join temp2 in db. classinfo
On temp1.classid equals temp2.classid
Where temp2.classname = classname
Select temp1;

Return query. tolist ();
}
}

If you want to output fields in two or more tables, you need to use an anonymous class or redefine an output object (inherit a table and add several missing fields)
Public static list <Object> getnewslist (INT classid)
{
Using (sitecmsdatacontext DB = new sitecmsdatacontext ())
{
VaR query = from temp1 in db. newsinfo
Join temp2 in db. classinfo
On temp1.classid equals temp2.classid
Where temp1.classid = classid
Select New
{
Newsid = temp1.newsid,
Title = temp1.title,
Classname = temp2.classname,
Content = temp1.content
};

// The following part cannot be used. Anonymous classes cannot be implicitly converted to object objects. If you use it directly in the Aspx. CS file, this problem does not exist.
List List = new list ();
foreach (VAR o in query. tolist ()
{< br> list. add (o);
}
return list;
}< BR >}

6. If there is a foreign key relationship between tables, the table objects in LINQ are automatically closed. This association is more convenient for multi-table queries.
Assume that table 1: newsinfo (field: newsid, title, classid, content) Table 2: classinfo (classid, classname)
The classid of table 2 is the foreign key of Table 1's classid (establishing the foreign key relationship between the two in SQL)

Public static list <newsinfo> getnewsbyclassname (string classname)
{
Using (sitecmsdatacontext DB = new sitecmsdatacontext ())
{
VaR query = from temp in db. newsinfo
WhereTemp. classinfo. classname= Classname
Select temp;

Return query. tolist ();
}
}

7. Use nested queries in LINQ (or the table structure of the news and news topic above)
Public static list <newsinfo> getnewsbyclassn (string classname)
{
Using (sitecmsdatacontext DB = new sitecmsdatacontext ())
{
VaR query = from temp in db. newsinfo
Where temp. classid = (from temp1 in db. classinfo
Where temp1.classname = classname
Select temp1). Single (). classid
Select temp;
Return query. tolist ();
}
}

8. Projection query using anonymous classes (with foreign key associations, for example, counting the total browsing times of all news categories [total browsing times of news under classification])
Public static list <Object> getnewsclassstat ()
{
Using (sitecmsdatacontext DB = new sitecmsdatacontext ())
{
VaR query = from temp in db. classinfo
Select New
{
Classid = temp. classid,
Classname = temp. classname,
Viewnum = temp. newsinfo. sum (W => W. viewnum)
};
List <Object> List = new list <Object> ();
Foreach (VAR o in query. tolist ())
{
List. Add (O );
}
Return list;
}
}

9. Extended query methods of LINQ (average, Count, sum, Max, Min, etc.)
Public static int getnewsavview ()
{< br> using (sitecmsdatacontext DB = new sitecmsdatacontext ()
{< br> int avview = (INT) dB. newsinfo. average (W => W. viewnum);
return avview;
}< BR >}

others:
1. Obtain the auto-increment ID of the newly inserted record (for example, log on automatically after registration, you need to use the auto-increment field to set the session.)
Public static int addnewsinfo (newsinfo entity)
{< br> using (sitecmsdatacontext DB = new sitecmsdatacontext ())
{< br> dB. newsinfo. insertonsubmit (entity);
dB. submitchanges ();
int newsid = entity. newsid;
return newsid;
}< BR >}