current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Dismiss
Announcing Stack Overflow Documentation

We started with Q&A. Technical documentation is next, and we need your help.

Whether you're a beginner or an experienced developer, you can contribute.

Sign up and start helping → Learn more about Documentation →
up vote 1 down vote favorite

I know this is a duplicate on SO, but I can't figure out how to use the contains operator in my specific code:

I have 5 bookings in the database:

ID, Booking, Pren, ReservationCode

1, VisitHere, 1, 1000A

2, VisitHere, 1, 1000A

3, VisitHere, 1, 1000A

4, VisitThere, 2, 2000A

5, VisitThere, 2, 2000A

    public int SpecialDelete(DataContext db, IEnumerable<BookingType> bookings) {

        var rescodes = (from b in bookings
                        select b).Distinct().ToArray();
        // Code Breaks here
        IEnumerable<BookingType> bookingsToDelete = db.GetTable<BookingType>().Where(b => bookings.Any(p => p.Pren == b.Pren && p.ReservationCode == b.ReservationCode));

        int deleted = bookingsToDelete.Count();
        db.GetTable<BookingType>().DeleteAllOnSubmit(bookingsToDelete);
        db.SubmitChanges();

        return deleted;
    }

When I pass the first record into this method (1, VisitHere, 1, 1000A), I want it to retrieve ids 1,2 and 3, but not 4 and 5.

I can do this by matching Pren and ReservationCode.

How can I do this as the .Any and .All operators are throwing the above exception?

Note: The method must accept a list of bookings because the argument will always be multiple bookings passed into the method, I just used a single booking as an example.

Edit: I basically need LINQ2SQL to generate a bunch of SQL statements like so (let's say I want to delete all records in my DB):

DELETE
FROM Bookings b
WHERE b.ReservationCode = '1000A' AND b.Pren = 1

DELETE
FROM Bookings b
WHERE b.ReservationCode = '2000A' AND b.Pren = 2
share|improve this question
    
Can you use the ID values of the bookings? – Gert Arnold Jul 19 '13 at 18:49
    
@GertArnold (even though I've marked the answer) No I can't use IDs I only get a header record (my DB is far more complex than I have written here) - So I get ID 1 and must delete 1,2 and 3. – Smithy Jul 22 '13 at 11:59
up vote 1 down vote accepted

The error you are getting is trying to direct you to use the .Contains method passing in a simple array. By default it translates that array into an In clause in the format:

Where foo In ("b1", "B2", "B3")

Notice here that you can't do a multi-dimentional array in the In clause (as you would need to do). Since you can't join server side to a local array, your options become limited as long as you have a composite key relationship.

If you don't need to fetch the rows in order to delete them, it will probably be faster anyway to just use Context's ExecuteCommand to issue your deletes. Just make sure to parameterize your query (see http://www.thinqlinq.com/Post.aspx/Title/Does-LINQ-to-SQL-eliminate-the-possibility-of-SQL-Injection)

string deleteQuery = "DELETE FROM Bookings b WHERE b.ReservationCode = {0} AND b.Pren = {1}";
foreach (var bookingType in bookings)
{
    db.ExecuteCommand(deleteQuery, bookingType.ReservationCode, bookingType.Preen);
}
share|improve this answer
    
This is what I have done only I potentially have 3000 records, I build the query inside the loop and then execute afterwards :) – Smithy Jul 20 '13 at 22:12
1  
You may want to chunk that down into batches of 100 records or so each. Assuming you are parameterizing the queries, SQL Server won't let you pass in 6000 parameters on a single query. Using string concatenation on the other hand opens you up to SQL Injection attacks. – Jim Wooley Jul 22 '13 at 13:30
    
Thanks again Jim, I sanitize my data upon input, which also comes from a trustworthy source so it shouldn't be a problem. – Smithy Jul 23 '13 at 10:08
up vote 1 down vote

What if you have a quasi temp table on the server. You can put the list values in there.

This is a real problem with ORMs. You have a lot is mismatch between local and remote capabilities.

I have tried even using .Range to generated a remote list to join against, but it doesn't work either.

Essentially you have to rearrange your data islands somehow ( i.e. where does the lists of pren and rs come from? Is it on the server somewhere ? ) or upload one of your local collections to a staging area on the server.

share|improve this answer
    
Your answer raises an interesting point, I've updated my answer with what I suppose I want L2S to do. For now I will just build and execute my own custom built SQL. – Smithy Jul 19 '13 at 17:16
up vote 0 down vote

The error message says "except the contains operator." Have you considered using the Contains operator? It should do the same thing.

So from 

IEnumerable<BookingType> bookingsToDelete = db.GetTable<BookingType>().Where(b => bookings.Any(p => p.Pren == b.Pren && p.ReservationCode == b.ReservationCode));

to

IEnumerable<BookingType> bookingsToDelete = db.GetTable<BookingType>().Where(b => bookings.Contains(p => p.Pren == b.Pren && p.ReservationCode == b.ReservationCode));

I realise that the list wont contain the same objects so you may need to do something like:

bookings.Select(booking => booking.PrimaryKeyOfAwesome).Contains(b => b.PrimaryKeyOfAwesome) etc etc.


Edited for clarity

Edit for humility Ok, so after actually recreating the entire setup I realised that my solution doesnt work because of the two parameter, not just one. Apologies. This is what I came up with in the end, which works, but is genuinely a terrible solution and should not be used. I include it here only for closure ;)

        public static int SpecialDelete(DataContext db, IEnumerable<BookingType> bookings)
        {
            var compositeKeys = bookings.Select(b => b.Pren.ToString() + b.ReservationCode).Distinct();
            IEnumerable<BookingType> bookingsToDelete = db.GetTable<BookingType>().Where(b => compositeKeys.Contains(b.Pren.ToString() + b.ReservationCode));

            int deleted = bookingsToDelete.Count();
            db.GetTable<BookingType>().DeleteAllOnSubmit(bookingsToDelete);
            db.SubmitChanges();

            return deleted;
        }
share|improve this answer
    
I have tried using the contains operator but it doesn't accept a predicate => query in the same way that the Any and All operators do. and your code doesn't compile :( – Smithy Jul 19 '13 at 16:33
    
Contains is translated to In (1,2,3,4) for LINQ to Sql. the TSQL construct for In doesn't support multiple parameters, thus you can't evaluate the complex lambda in contains this way. – Jim Wooley Jul 19 '13 at 20:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.