Error deleting field in Visual Studio 2013 mdf

I created a Linq to Sql class and an dbml from a LocalDB mdf.

I want to delete a field from the database.

I get this error:

Update cannot proceed due to validation errors.  
Please correct the following errors and try again.

SQL71501 :: Index: [dbo].[MemberPanTickets].[MemberPanTickets$Identifiers] has an unresolved reference to Column [dbo].[MemberPanTickets].[Location].

The field is a text field. Is not part of any relationships. But, does have an index. I get the same error when only trying to delete the index.

I've deleted the field from the dbml diagram but still get the error.

How do I find and remove the reference that's causing this error?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy DownsOWNERCommented:
Try this.

right click on the DB project in VS you get the 'Publish Database' dialog. You setup the target server connection and choose the correct database.

Then click on the 'Advanced...' button to open up the 'Advanced Publish Settings'.

1) Uncheck - 'Block incremental deployment if data loss might occur' checkbox.
2) Check - 'DROP objects in target but not in project'

Open in new window

Bob LearnedCommented:
LINQ to SQL is a dying breed.  I found myself deleting the DBML, and recreating it again.  You should look at Entity Framework 6, because it is a living system, and a more mature product.
KapriceAuthor Commented:
Thanks for the tip on EF, Bob. I was really tempted to go that way, but I'm still new to VS and read that EF is more complicated than LinqToSql and since this is a fairly simple data structure, I wanted to reduce the amount of new stuff I had to learn. But, it's good to know that LinqToSql is outdated. I'll focus on EF for my next project.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

KapriceAuthor Commented:
Randy, I'll give that a try and report back. Thanks for the tip.
KapriceAuthor Commented:
Randy, I've gone through the thread you linked to and I'm confused.

It appears this process creates a script for creating a copy of the database. Is that right? How does that help me drop a field? And, what do I do with the script once it's generated?
KapriceAuthor Commented:
Bob, are you certain my error is due to dbml?
Bob LearnedCommented:
I am not 100% certain, but it feels like there is a mess of plumbing that is generated, that you would need to find.  If you have heavily customized the data context, then I don't think that you would want to re-generate.  

One suggestion would be to backup all the files related to the DBML file, delete them, and then re-generate.
KapriceAuthor Commented:
Bob, I get the error when trying to drop a field from the database (LocalDB). You're saying the error is because my dbml has a reference that's blocking VS from processing it?
Bob LearnedCommented:
No, I thought you had an error with the data context for LINQ to SQL, not the local database.  Are you trying to use Code First with LINQ to SQL (from Entity Framework)?

If not, what are you using to delete the column from the table?
KapriceAuthor Commented:
No, not Code First.

I'm using Visual Studio Data Connections in Server Explorer.
Bob LearnedCommented:
OK, is the column that you need to delete part of a relationship?  If so, then you need to delete that relationship first.
KapriceAuthor Commented:
I appreciate your help, but if you'll re-read my OP, you'll get a better understanding of the situation.
Bob LearnedCommented:
Heck, that misunderstanding happens after answering so many questions...

I have no idea why this is happening, but I am willing to help you find a solution.  What is the order of operations for the delete?  Are you deleting the index, before the column?
KapriceAuthor Commented:
Well, I wasn't doing anything other than dropping the table (in Visual Studio) and hitting the Update Database button.

What I ended up doing is taking the code that VS created (which really just created the table, constraints, indices, and extended properties) all over again and pasted it into Management Studio.

I deleted the original table and ran the code. When it errored out, I either fixed the issue in MS or just skipped that line.

Some of the errors were due to a dropped field. Some were due a an existing index or constraint.

Then I used a 3rd party tool to update my dbml.

It wasn't until I went through that process that I even came close to understanding that the error was due to other elements of the table. (And I still don't really understand that.)

So, I think this question is resolved. What's the appropriate way to mark it as complete? Should I just accept one of your comments? Or, mark my own comment as the solution.

I'm okay with either one. I'd just like the information to be useful to anyone who might need it in the future.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob LearnedCommented:
I would suggest marking your own comment as the correct answer.
KapriceAuthor Commented:
Thanks again for all your efforts.
KapriceAuthor Commented:
Solution found and listed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.