Convert my SQL to LINQ

Question

this is my code that I write it but I want to use LINQ , can you help me for conver this code to LINQ ?

/// <summary>
/// Stores the request in the PublisherRequests table and
/// the selected categories in the PubReq_Categories table.
/// </summary>
/// <param name="UserId">The user's ID logged in.</param>
/// <param name="Url">URL of the site that you want show the banner on it.</param>
/// <param name="TypeId">The type id of the banner type you want to show.</param>
/// <param name="CategoryIds">A set of category IDs depend on your banner.</param>
/// <returns></returns>

public static int PutRequest(string UserId,string Url,int TypeId,int[] CategoryIds)
{
    int reqId;

    using ( SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings[0].ConnectionString) )
    {
        //Retrieves the last RequestId from database.
        string selectLastReq = "SELECT TOP 1 RequestId FROM PublisherRequests WHERE UserId=" +
            UserId.ToString() + "ORDER BY RequestId DESC";
        SqlCommand cmdSelect = new SqlCommand(selectLastReq, cnn);

        //For inserting the new request in the database.
        string insertRequest = string.Format("INSERT INTO PublisherRequests(" +
            "RequestId, UserId,PageUrl, TypeId) VALUES ('{0}','{1}',{2})",
            UserId, Url, TypeId);
        SqlCommand cmdInsert = new SqlCommand(insertRequest, cnn);

        //For inserting each category for the request on a new row.
        string insertReqCat = string.Format("INSERT INTO PubReq_Categories(" +
            "RequestId, CategoryId) VALUES (@ReqId,@CatId)");
        SqlCommand cmdInsertReqCat = new SqlCommand(insertReqCat, cnn);
        cmdInsertReqCat.Parameters.Add("@ReqId", SqlDbType.Int);
        cmdInsertReqCat.Parameters.Add("@CatId", SqlDbType.Int);

        cnn.Open();

        //New request insertion.
        cmdInsert.ExecuteNonQuery();

        //Retrieve last request number.
        reqId = Convert.ToInt32(cmdSelect.ExecuteScalar());

        //Categories insertion.
        cmdInsertReqCat.Parameters["@ReqId"].Value = reqId;
        foreach ( int catId in CategoryIds )
        {
            cmdInsertReqCat.Parameters["@CatId"].Value = catId;
            cmdInsertReqCat.ExecuteNonQuery();
        }
    }

    return reqId;
}

Answer 1

Assuming that you're using the designer, that the RequestId column is auto-generated, you have an association between the two classes, and your classes are named as below, I think this will work.

using (var context = new DBDataContext())
{
    var request = new PublisherRequest
                  {
                     UserId = UserId,
                     PageUrl = Url,
                     TypeId = TypeId
                  };

    for (var categoryId in CategoryIds)
    {
         request.PubReqCategories.Add( new PubReqCategory
                                       {
                                           CategoryId = categoryId 
                                       } );
    }

    context.PublisherRequests.InsertOnSubmit( request );
    context.SubmitChanges();
}