Delete a sql row using Linq in C#

Question

I am trying to delete a sql table record from C# thru Linq but some reason DeleteonSubmit is not recoganized,am not sure what iam missing here, please guide me the right way

here is my code

          proxy = new ServiceClient("basicHttp_IService");
        //GatewayService.ServiceClient proxy = new ServiceClient("basicHttp_IService");

        SearchCriteria criteria = new SearchCriteria();
        criteria.UserRoles = new string[] { "*" };

        var stories = proxy.GetStoryItemsByCriteria(criteria);
        var programs = proxy.GetPrograms();
        var Profiles = proxy.GetProfiles();
foreach(StoryProgram sp in lstStoriestoClose)
{
    try
    {
        DateTime LastUpdateTimestamp;
        DateTime CurrentTime = DateTime.Now;
        LastUpdateTimestamp = sp.Story.LastUpdatedOn;

        if ((CurrentTime - LastUpdateTimestamp).TotalHours > 24)
        {
            //Delete the story from database
            var storytoDelete = from story in stories
                                where story.Id == sp.Story.Id
                                select story;

            //I am trying to delete the record like below
            stories.DeleteonSubmit(storytoDelete);
            stories.SubmitChanges();
            //Intellisense doesn't show the option DeleteonSubmit and SubmitChanges
        }
    }
}

Please guide me the right way to delete the record in SQL thru Linq

do you see the record if you stop after assignment of storytodelete? — aw04
– aw04, Commented Feb 25, 2014 at 23:02
i don't see a record,i can see a record adding this code after storytodelete "List<StoryProgram> lstStoriestoDelete = (from story in storytoDelete join program in programs on story.ProgramId equals program.Id join profile in Profiles on story.ProfileId equals profile.Id select new StoryProgram(story, program, profile)).ToList(); " — Usher
– Usher, Commented Feb 25, 2014 at 23:23
my problem is, it throws an exception at DeleteonSubmit or DeleteAllonSubmit "System.Array doesn't contain a definition for DeleteALLonSubmit and no extension method DeleteALLonSubmit accepting a first argument of type System.Array are you missing a using directive or an assembly reference" — Usher
– Usher, Commented Feb 25, 2014 at 23:24
please show where stories is being defined, it should not be an array — aw04
– aw04, Commented Feb 25, 2014 at 23:28

D Stanley · Accepted Answer · 2014-02-25 23:08:49Z

6

DeleteOnSubmit is for single entities. Your query returns a collection of entities (granted there may be only one entry, but it's still a collection). You can either use DeleteAllOnSubmit:

//Delete the story from database
var storytoDelete = from story in stories
                    where story.Id == sp.Story.Id
                    select story;

//I am trying to delete the record like below
stories.DeleteAllOnSubmit(storytoDelete);

or explicitly extract one record:

//Delete the story from database
var storytoDelete = from story in stories
                    where story.Id == sp.Story.Id
                    select story;

//I am trying to delete the record like below
stories.DeleteOnSubmit(storytoDelete.Single());  // or First, depending on whether you expect more than one match

answered Feb 25, 2014 at 23:08

D Stanley

153k12 gold badges189 silver badges257 bronze badges

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

7 Comments

Usher Over a year ago

thanks for you comment,right now my problem is, it throws an exception at DeleteonSubmit or DeleteAllonSubmit "System.Array doesn't contain a definition for DeleteALLonSubmit and no extension method DeleteALLonSubmit accepting a first argument of type System.Array"

Usher Over a year ago

Even i added Namespace "using System.Linq; using System.Data.Sql;

D Stanley Over a year ago

C# is case sensitive. Check your capitalization.

Usher Over a year ago

intellisense doesn't show an option like Delete after stories.

D Stanley Over a year ago

OK Now that you've added more context I can see that stories is some sort of collection returned from your Proxy. Does your Proxy have some sort of Delete method on it?

|

aw04 · Accepted Answer · 2014-02-25 23:40:14Z

1

It looks like your service is returning an array and not a valid linq database object. This is why it does not recognize the methods you expect to see. You need to examine the type and go from there.

You can always right click the service reference and configure to check/set the return type.

edited Feb 25, 2014 at 23:40

answered Feb 25, 2014 at 23:34

aw04

11.2k11 gold badges60 silver badges92 bronze badges

3 Comments

Usher Over a year ago

True ,its returning array for sure.

Usher Over a year ago

Thanks for the suggestion,do your recommend to change the collection type to Generic.list will do the job ?

aw04 Over a year ago

No it's not that simple. You need the actual type that relates to you're database table.

Usher · Accepted Answer · 2014-02-26 03:04:27Z

I just added delete functionality in WCF service and pass the sql record details to delete the record from SQL that solved my problem.

Thanks all for the suggestion and advise.

 foreach(StoryProgram sp in lstStoriestoClose)
        {
            try
            {
                DateTime LastUpdateTimestamp;
                DateTime CurrentTime = DateTime.Now;
                LastUpdateTimestamp = sp.Story.LastUpdatedOn;
                if ((CurrentTime - LastUpdateTimestamp).TotalHours > 24)
                {
                    //Delete the story from database
                    //Check the gateway to delete the record in the db.
                    var storytoDelete= from story in stories
                                        where story.Id== sp.Story.Id
                                        select story;

                   // stories.DeleteAllonSubmit(storytoDelete);
                    List<StoryProgram> lstStoriestoDelete = (from story in storytoDelete
                                                            join program in programs on story.ProgramId equals program.Id
                                                            join profile in Profiles on story.ProfileId equals profile.Id
                                                            select new StoryProgram(story, program, profile)).ToList();
                    foreach (StoryProgram sps in lstStoriestoDelete)
                    {
                        try
                        {
                            proxy.DeleteStoryItem(sps.Story.Id);
                        }

Collectives™ on Stack Overflow

Delete a sql row using Linq in C#

3 Answers 3

7 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related