none
Query processor error "The ntext and image data types...."

    Question

  •  

    Hi! I get query processor error

     

    The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses, except when these data types are used with the LIKE or IS NULL predicates.

     

    For a table definition (Copyright) that only consists of strings (Language_Name and Version are keys).

     

    Code Snippet

    this.Name = _db.Copyright.Where(c => c.Language_Name == languageName && c.Version == version).First().Name;

     

     

     

    This code executed fine in the SQL Server Compact 3.5 SP 1 Beta, but fails after upgrade to SP 1?

     

    I have tried various ways to query, but always get this error message now. Any suggestions for circumvent the error?

     

    I am using the Binary-type in another objectcontext, and no tables in the _db objectcontext contains binary or ntext

     

    I tried to the create a console application that only access the Copyright-table, and this works ok.

     

    Henning

    Thursday, August 14, 2008 12:47 PM

Answers

  • We are working on this issue.

     

    The problem is not with the table but with the parameter. There was an earlier bug in the beta phase where one could not populate blob columns and would get an error saying that it can't insert values greater than 8000 bytes.

     

    In a fix for that, another issue got crept in where the parameter that you pass gets type casted as ntext instead of nvarchar, thats why you see this error.

     

    Until we resolve the issue, I  can suggest you following work-arounds:

     

    1. This one seems to be the best amongst the worse. Substitute the value of the string in the Where expression. Something like this. Move the where clause with the customers, use string concatenation to generate the query with the value subsituted instead of using parameter.

     

    string str = "Sales Representative";

    var query = from cust in context.Customers.Where("it.ContactTitle == '" + str + "'")

    select cust;

     

    2.  Which you are already aware of, i.e. instead of parameters use direct values in queries like "foo". But that does not solve all isues. And above one is better than this if you want to do this in a loop and to avoid hard-coding things.

     

    3. This is worst-performance work-around. Select all objects and inside your foreach/for loop use a if condition to work on only those objects. Here you will have to go over all the entities. Not good if number of entities are huge.

     

    4. In Object Queries, instead of just passing the string, use a Parameter and define it's facets either as fixed length or as a variable length with a Max Value < 8000. This will make the parameter to be "nchar" or "nvarchar" and your scenarios should work. (I am not sure if this is gonna work, as the logic to decide the parameter is at the provider level and not application level, and there aren't much public apis around the application layer to set the facets. You can only specify System.String etc types). If you have already done this in your application, I would like to know a way Smile

     

    Your comments/feedbacks are valuable to us. Thanks

     

    [PS: This post is "as is" without any warranties and confers no rights]

    Monday, August 18, 2008 2:30 PM
  • Check that your SQL Compact DLL files are version 3.5.5692.1. If not, then remove the 3.5 SP1 runtime, and install the hotfix.
    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com Please mark as answer, if this was it.
    Wednesday, April 22, 2009 11:43 AM

All replies

  • Have you tried to use the datacontext Log method to inspect the offending SQL statement...

     

     

    Friday, August 15, 2008 5:36 AM
  • Thanks for reporting this Henning, we are currently looking into this issue.

     

    Meanwhile can you try if this works for you?

    this.Name = _db.Copyright.Where(c => c.Language_Name == "english" && c.Version == "5.0").First().Name;

     

    Instead of parameters use the value directly in the query.

    Monday, August 18, 2008 7:37 AM
  • I'm having the same issue after upgrading to SP1.

     

    entities.Employee.FirstOrDefault(p => p.UserName == username)

     

    Employee is a table without any ntext or image columns, but I get

     

    "The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses, except when these data types are used with the LIKE or IS NULL predicates."

     

    Stack trace:

       at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
       at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
       at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
       at System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__1[TResult](IEnumerable`1 sequence)
       at System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
       at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.ExecuteSleep(Expression expression)
       at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)

    ...

     

    entities.Employee.FirstOrDefault(p => p.UserName == "foo")  works, but that doesn't help me much.

     

     

     

    Monday, August 18, 2008 11:57 AM
  • We are working on this issue.

     

    The problem is not with the table but with the parameter. There was an earlier bug in the beta phase where one could not populate blob columns and would get an error saying that it can't insert values greater than 8000 bytes.

     

    In a fix for that, another issue got crept in where the parameter that you pass gets type casted as ntext instead of nvarchar, thats why you see this error.

     

    Until we resolve the issue, I  can suggest you following work-arounds:

     

    1. This one seems to be the best amongst the worse. Substitute the value of the string in the Where expression. Something like this. Move the where clause with the customers, use string concatenation to generate the query with the value subsituted instead of using parameter.

     

    string str = "Sales Representative";

    var query = from cust in context.Customers.Where("it.ContactTitle == '" + str + "'")

    select cust;

     

    2.  Which you are already aware of, i.e. instead of parameters use direct values in queries like "foo". But that does not solve all isues. And above one is better than this if you want to do this in a loop and to avoid hard-coding things.

     

    3. This is worst-performance work-around. Select all objects and inside your foreach/for loop use a if condition to work on only those objects. Here you will have to go over all the entities. Not good if number of entities are huge.

     

    4. In Object Queries, instead of just passing the string, use a Parameter and define it's facets either as fixed length or as a variable length with a Max Value < 8000. This will make the parameter to be "nchar" or "nvarchar" and your scenarios should work. (I am not sure if this is gonna work, as the logic to decide the parameter is at the provider level and not application level, and there aren't much public apis around the application layer to set the facets. You can only specify System.String etc types). If you have already done this in your application, I would like to know a way Smile

     

    Your comments/feedbacks are valuable to us. Thanks

     

    [PS: This post is "as is" without any warranties and confers no rights]

    Monday, August 18, 2008 2:30 PM
  • I'm using ADO.Net to entities over a SQL Compact Database and I encountered the same issue.

    In order to solve the problem, I transformed my code from :

                    Item existing = (from i in
    m_entities.ItemsSet
                                    where
                                        author == i.Author &&
                                        keywords == i.Keywords &&
                                        description == i.Description
                                    select i).FirstOrDefault();

    To
                var allItems = m_entities.ItemsSet.ToList();

                    Item existing = (from i in allItems
                                     where
                                        author == i.Author &&
                                        keywords == i.Keywords &&
                                        description == i.Description
                                     select i).FirstOrDefault();

    In this case, the whole table is loaded but in my case it was not a problem as I have only few hundreds records, and the query is called rarely.


    Ravi, you tell "
    4. In Object Queries, instead of just passing the string, use a Parameter and define it's facets either as fixed length or as a variable length with a Max Value < 8000. This will make the parameter to be "nchar" or "nvarchar" and your scenarios should work. (I am not sure if this is gonna work, as the logic to decide the parameter is at the provider level and not application level, and there aren't much public apis around the application layer to set the facets. You can only specify System.String etc types). If you have already done this in your application, I would like to know a way "

    Is it feasable using ADO.Net to entities ?

    Thanks,
    Steve
    Monday, August 18, 2008 4:33 PM
  • Yes, that works, but I have several queries that are similar and prefer to use your work-around nr. 1 at the moment.

    Code Snippet

    this.Name = this.DB.Copyright.Where("it.Version = '"+version +"' AND it.Language_Name ='"+languageName+"'").First().Name;

     

     

    Henning

     

    Monday, August 18, 2008 5:07 PM
  • I experimented a bit with the ToTraceString, but the SQL seems ok

     

    Monday, August 18, 2008 5:10 PM
  • @Steve

    The work-around No 1 that I provided above should help you here than going over all 100 entities. Coming back to your question regarding "setting of facets", I spent a lot of time figuring out a way to do this but not have been successful yet.

    The logic that I wrote is what the EF providers do based on facets, i.e. In case of Edm..String type, if fixed length make nchar, for variable use nvarchar and if length is max then go for ntext/nvarchar(max). But as I see from the various provider codes they do make parameters of all types from facets, what is bothering me is that there isn't much documented public api support to set the facets at application level. The metadata collection of parameter is a read-only field. TypeUsage and EdmType have not been any useful.

    I will talk to the EF folks to check if there is a way to do that, that would solve lot of issues around parameters. If there is any way to do that I will post here about it.

     

    @Henning

    I am glad that the work-around No 1 helped you. The T-SQL generated in this case is perfectly fine, it's the parameter type that causes the issue.

     

    In first case which does not work, t-sql with parameter is created

    select ..... from .... where ..= @p__linq__1

    whereas in the second case of work-around, the t-sql looks like

    select ..... from ... where .... = N'value'

     

    @All

    Feel free to ping/trouble me for any issues that you face, will be glad to help.

     

    Regards

    Tuesday, August 19, 2008 5:47 AM
  •  

    My first steps in EF and its sql compact provider let me feel that is not a "finished" technology. I know this is a first release, but when I tried to implement my DB the way I used to (identity seed as PK, for example).

     

    Is there any list of severe limitations of the EF + SQL COmpact couple ? The faq is a bit long and contains also informations entries.

     

    But at least, I'm still enjoying working with "toys" microsoft give to us Smile

     

    Steve

    Wednesday, August 20, 2008 4:07 PM
  • Glad you are enjoying working with "toys". Your feedback is always welcome to improve our product.

     

    Yes, there are few limitations in the v1 of the product, you need to go through read me so that you know what can and what cannot be done (The beta version also had a read me, it had other topics around EF).

    http://go.microsoft.com/fwlink/?LinkId=79942 (latest)

    http://www.microsoft.com/downloads/details.aspx?FamilyId=68539FAE-CF03-4C3B-AEDA-769CC205FE5F&displaylang=en (beta)

     

    As far as PK is concerned, there is already thread going around on server-generated keys, please refer there for more insight into that:

    http://forums.microsoft.com/forums/ShowPost.aspx?PostID=3758692&SiteID=1

     

    You can track our team blog and Steve Lasker's blog time to time for updates around these.

    http://blogs.msdn.com/sqlservercompact

    http://blogs.msdn.com/stevelasker/

    Thursday, August 21, 2008 5:21 AM
  • Do you have any idea when a fix for this might be available? While your workaround is possible to implement I'd rather avoid having to do it, so I want to have an idea of for how long I would end up waiting for a fix. In our company we're doing a few SQL Compact projects, and right now this issue is preventing us from moving to SP1 of Visual Studio 2008. Which is bad, because there's a bunch of stuff in there that we really need.
    Thursday, September 04, 2008 4:03 PM
  • Hey guys, what is going on with the fix. We've been wayting 2 months now.
    Any idea when we can expect it?
    Monday, October 13, 2008 7:32 PM
  • Any updates on a fix for this issue?

    This bug is giving me grief.  I'm working with a sizable database and more complicated LINQ queries, so the provided work arounds haven't worked for all of my queries...
    Thursday, October 16, 2008 6:28 PM
  • We are also waiting for the fix - as others have mentioned the workarounds above, allthough appreciated, are not suitable for all queries. When can we expect this?
    Wednesday, October 22, 2008 3:40 PM
  • As much as I want you guys to be un-blocked as early as possible, there isn't any definitive date that I can provide as of now. We have the fixes, but as this is going be a public release there are several other aspects in addition to the fix that are needed to be figured out. I would request you to be patient for a little while more, your should hear something soon..... and I sincerely hope it's good Smile

     

    Wednesday, October 22, 2008 4:42 PM
  • Hi Ravi,
        We need to go to production by Jan, do you think you will have the fix before that. 
    Regards
    Siva
    Friday, November 21, 2008 10:24 PM
  • Is the fix for this still unavailable?
    Monday, December 22, 2008 9:28 PM
  • Any news on this fix?  It's already been about 5 months.  Please give us some kind of update.  It's very embarrassing convincing everyone to go with SqlCe 3.5 Sp1 and use the Entity Framework for this project and now it's all blowing up in my face.  People are not satisfied with the workaround.

    Do you guys do unit testing?  I would think unit testing would have picked up on this sort of problem before you shipped it.
    Tuesday, January 06, 2009 11:42 AM
  • Hi Everyone,

    The problem is not the fix but the servicing story. The fix was identified within the first few days when the problem was reported. The fix was only in the EF provider portion of SQLCE and nothing changed in the main product code, which made it even harder to make a public release as many other people are using the SP1 RTM code in scenarios other than EF too.

    For those who are blocked especially on the production side, I would suggest you to please ask for the hotfix from the Microsoft CSS/PSS, they have it with them.

    A more official post will be made on the exact process of getting the fix.
    Extermely sorry for this delay, we are equally feeling bad about it.

    Regards
    Ravi


    SDET, SQL CE. (Please hit Yes if this was the answer to your question)
    Tuesday, January 06, 2009 12:32 PM
  • I contacted Microsoft support to obtain the hotfix and was asked for the Microsoft article number.  Can someone please post this?
    Wednesday, January 14, 2009 2:18 PM
  • I would also like to know what is the KB number of the fix for this huge error !
    Tuesday, January 27, 2009 2:26 PM
  • Hi Ravi,

    We are blocked on the production side. How do we ask for the hotfix from the Microsoft CSS/PSS, as you suggest? What is their URL (or e-mail address) and how do we identify the hotfix to them?

    Thanks,

    DJ
    Tuesday, February 03, 2009 2:17 AM
  • I understand everyone's concern here. I am in the product development team which means I have very little control over the publishing of KB Article :(.
    For some reason, the hotfix was marked as Microsoft Interal because of which it did not go live, the support team is working on that.

    Meanwhile I have the KB number and Hotfix number with me, please use that with the CSS to obtain the fix.

    The website to search fixes is: http://support.microsoft.com/search/?adv=1
    There is a link to contact CSS directly.

    Bug ID: 50003448
    KB Article: 958478
    Fix ID: 361486

    The KB article will ideally have all the information around the fix. However, I will put a brief overview here on what we did.
    The fix is only in the System.Data.SqlServerCe.Entity dll, so rest of the product remains exactly same as SQLCE 3.5 SP1.

    You will have to uninstall the previous installation of SQLCE 3.5 SP1 in order to install the new MSI (obtained through KB).

    This hotfix takes care of two issues:
    1. The ntext and image datatype being assigned to any Edm String type, which results in error at QP (Query Processor) level whenever we use that parameter in Order By, Group By or in Predicates. The one in question in this thread.
    2. Fixing the parsing errors around the extra "AS" token when anyone writes queries which use Scalar Subquery to Apply conversion.

    To fix the issue 1, what we did was to push the parameter type guessing to QP level rather than deciding it at EF level for String and Binary Data types. Though this fixes the issue in question it also breaks a lower priority scenario which used to work earlier.

    The present design of QP's parameter data type guessing works very well if the parameter is equated to or used along some pre-defined/implicit data type. But in cases where parameter type cannot be judged we throw an error. For Eg: Select @param1 from Table1. If you don't specify @param1's data type, you will get an error.

    So any EF queries that can result into T-SQL like this will get errors saying that parameter is not defined. This will effect only String and Binary data types.

    Eg for such query:
    string s = "Hello World";
    var q = from c in customers
                select s;

    Here resultant T-SQL should be like: select @p__linq_1 from Customers

    As we skipped setting data type for @p__linq_1 due to new logic, one should get an error saying: " A parameter is not allowed in this location. Ensure that the '@' sign is in a valid location or that parameters are valid at all in this SQL statement. [,,,,,]"

    We already have a DCR filed to fix this in QP, but it will be time before this is fixed.

    Hope this helps. For any doubts, questions, queries please feel free to ask. Do let us know the feedback, it is really very important from the product's perspective and will help us do better things in future.

    Cheers


    SDET, SQL CE. (Please hit Yes if this was the answer to your question)
    Tuesday, February 03, 2009 5:23 AM
  • Tried searching at the page at the URL you provided and no matter what I try it always says, "There are no documents that match your search for..."

    The link to contact CSS directly on top right leads to more pages with a bewildering number of options. Still don't seem to find anything that relates to SSCE or EF. Why is it so hard to obtain this hot fix? Is there no straight forward link to the download or an e-mail address to quickly ask for it? Already spent 15 minutes and I have nothing to show. Pretty frustrating.

    DJ
    Monday, February 09, 2009 5:17 PM
  • The KB article still does not seem to be up thats why you did not find it there. Would have been lot easier otherwise.

    After you go to this link: http://support.microsoft.com/search/?adv=1
    You see this link at right top side: http://support.microsoft.com/gethelp/default.aspx?content=gp;en-us;advsearch

    (To do this, you need to log in with your windows live account (can be setup with any email) and also specifying your country, as phone numbers, charges are different for each country and work timings too.)

    Upon clicking it you see all of products listed there. SQL Compact is not listed separately as it ships with Visual Studio and SQL Server.
    Choose any of SQL Server 2008 or Visual Studio 2008, from the options available.

    This will take you to a page which will give you option for emailing the support folks or making them the call or asking the fix online.
    If you own any of these products, depending upon the agreement, you will get this for free.

    You can quote the KB/Fix details that I have mentioned above. If you still are facing issues, let me know.

    I will post here whenever the KB article is up separately.

    Ravi
    SDET, SQL CE. (Please hit Yes if this was the answer to your question)
    Tuesday, February 10, 2009 8:59 AM
  • I got to the point where it says, "Provide Product ID" but when I enter the ID for VS.NET 2008 Pro it just says, "All no-charge requests have been used
    All no-charge support requests available for this product have been used. Please enter another Product ID or choose another support option below."

    What's odd is that I have never before requested support on this product (and it says the product should get two freebies). So at this point still not able to obtain the hot fix, as I don't want to pay $99 just to send an e-mail asking for the hot fix. Any other suggestions for obtaining the fix?

    If there is just no way to get it right now, can you advise on how to issue a case-insensitive LINQ query on the string values of an SSCE entity since we can't use field.ToLower = value.ToLower?

    So far, all I've gotten to work is to use LIKE, but need more precision, and also not to always have to operate on an entity; e.g.,

    <entity>.WHERE("it.{0} LIKE '%{1}%'", fieldName, fieldValue)

    DJ
    Thursday, February 12, 2009 11:43 PM
  • Hmm, I never really got to that level of details (never used it).

    Anyways, I cleared it up internally, this fix should be available free of charge to everyone.

    The public KB article is on its way, it is going through final reviews and should be up soon. Until the article containing the hotfix is published for public consumption, you can make a call to CSS (800) 956-5800 between 8:00 AM EDT and 6:00 PM PST, monday through friday and open a no-charge incident to get the fix.

    For the like thing, I will have to check out if there is a better way.
    SDET, SQL CE. (Please hit Yes if this was the answer to your question)
    Friday, February 13, 2009 4:36 AM
  • You can now get the hotfix for 2 Entity Framework issues with SQL Compact here: http://support.microsoft.com/default.aspx?scid=kb;en-us;958478&sd=rss&spid=2855
    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com Please mark as answer, if this was it.
    • Proposed as answer by Steve B_ Friday, February 20, 2009 8:31 AM
    Thursday, February 19, 2009 8:21 PM
  • Great news that the hotfix is available, but I wonder if the hotfix can be shipped as part of an free internet application (a freeware I'm building).


    And second question: what is the roadmap for future releases or service packs of SQL Compact and EF ? (in fact, if the next SP is planned in few weeks, I'll build my app over sql compact, if not I'll havve to change the DB engine)...


    Thanks,
    Steve


    Friday, February 20, 2009 8:34 AM
  • Steve,

    Please check the EULA and redist docs in the install directory. It states what all you can publish along with your application. I don't think there should be any issues on what you are planning, but do go through the documents/licenses/agreements.

    On the second question, there is no information available on next SP. The next major release of EF should be along with next .net framework version which is in line with next version of Visual Studio. Coming to SQLCE, if there are any fixes that need to be out to the public, they will be made available accordingly.
    Member, SQL Server Compact. (Please hit Yes if this was the answer to your question) "The above post is "AS IS" without warranties and confers no rights."
    Friday, February 20, 2009 9:02 AM
  • Is there a 64-bit version of the hotfix?
    Sunday, February 22, 2009 3:34 AM
  • It's now almost March. Please tell us how/where to get the hotfix. I have MSDN Premier subscription. Where do I start?

    Again, is this is a Visual Studio 2008 / SQL COmpact post-SP1 HOTFIX? Does it fix the the issue with the following query as well?

    var query = from c in service.tblCityPostalCodes

    where c.StateProvinceCode == StateCode

    select c;

    if (query != null)

    {

    var data = from c in query.AsEnumerable()

    orderby c.City

    select new { CityBind = c.City + "-" + c.PostalCode, ZipBind = c.PostalCode };

    cboCity.DataSource = data.ToList();

    cboCity.DisplayMember = "CityBind";

    cboCity.ValueMember = "ZipBind";

    }

    Sunday, February 22, 2009 6:14 PM
  • Got to the hotfix url listed above. Click: Hotfix Download Available View and request hotfix downloads to download the hotfix. Give your email and you will reeive the download link by email. Thats' it.
    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com Please mark as answer, if this was it.
    Sunday, February 22, 2009 7:12 PM
  • Hotfix download works, but what do I do if I want Native x64? I have to uninstall SP1 which was the version which introduced support for x64 and the hotfix doesn't look like it installs support for x64.
    Thursday, February 26, 2009 6:09 AM
  • Hello,

    I still can't resolve this problem. I'm using ADO.NET and SQL Compact 3.5 SP1. I try to do a linq query to my ADO.NET entities and it works with a hard coded parameter but not with a variable-parameter if I set the exactly same string into a variable. The difference is that I don't get any error messages.

    So the upper one works fine, whereas the lower one doesn't:

     


    TaskListEntities2 db = new TaskListEntities2();

    -----------------------
    var
    _tasks = from t in db.Task

     

     

    where t.Description.Contains("toimi")

     

     

    select t;

     

     


    -----------------------

    string

     

    searchtext = "toimi";

     



    var

     



    var

     

     

    _tasks = from t in db.Task

     

     

    where t.Description.Contains(searchtext)

     

     

    select t;

    ------------------------

     

     

     





    I don't think I can use any string replacement with "==" -signs, as I want to search among the text, not any exact string.


    The hotfix, which is referred above, says "a newer version of SQL compact is already installed", so it doesn't help.


    /Henri



    Wednesday, April 22, 2009 11:39 AM
  • Check that your SQL Compact DLL files are version 3.5.5692.1. If not, then remove the 3.5 SP1 runtime, and install the hotfix.
    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com Please mark as answer, if this was it.
    Wednesday, April 22, 2009 11:43 AM
  • Yeps, thanks. Now it works! I removed SQL compact SP1 and run the hotfix. 

    Do I have to care about SP1 now? Control panel says it's installed even though I didn't intentionally install it...weird.



    /Henri
    Wednesday, April 22, 2009 1:36 PM
  • The hotfix "replaces" SP1, and appears as such in Control Panel..
    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com Please mark as answer, if this was it.
    Wednesday, April 22, 2009 2:27 PM
  • im also having this problem.. its been over six months and still no public fix for this?
    this bug renders sql ce unusable in any but the most trivial applications.. not beeing able to use variables in queries? thats a very very basic feature....

    the hotfix works but its hardly a good deployment story..
    Sunday, May 17, 2009 6:51 PM
  • im also having this problem.. its been over six months and still no public fix for this?
    this bug renders sql ce unusable in any but the most trivial applications.. not beeing able to use variables in queries? thats a very very basic feature....

    the hotfix works but its hardly a good deployment story..

    This summarizes the issue. I am not even trying the hotfix as I do not think it fits in a ClickOnce deployment scenario.

    I really appreciate the effort Microsoft is taking to leverage the programing model through the investment in LINQ To XXX and other great technologies. But the recommended route involving:
    1. Adopt Linq To SQL because is great.
    2. Upgrade to Linq to Entities because Linq To Sql is to be discontinued and the former will be better.
    3. Make all kinds of workarounds to fill the gap in basic features because Linq To Entities is not a finished technology.

    has been rather a misleading one.

    (Sorry if this is cross-topic.)
    Wednesday, June 10, 2009 1:06 PM
  • im also having this problem.. its been over six months and still no public fix for this?
    this bug renders sql ce unusable in any but the most trivial applications.. not beeing able to use variables in queries? thats a very very basic feature....

    the hotfix works but its hardly a good deployment story..

    I've seen a microsoft web cast where the presenter include the SQL Compact dll directlty within the application folder.
    Using this scenario, you can include the hotfixed sql dll directly into the setup...

    Not sure it is a best practice but it may work.

    Other alternatives is to use non MS DB, such sqlite,... but I did not tried.

    Steve
    Wednesday, June 10, 2009 2:21 PM
  • Steve brought up a very good point here. SQL Server Compact supports multiple modes of deployment, central - which involves installing of MSI requiring Admin privileges, or Private - where you can directly include the Compact Dlls with your application without any need to install or uninstall.

    On the other hand, coming to deployment story for SQL CE 3.5 SP1 especially in this scenario. This particular build that had this QFE fix had MSI versioning issues which made it difficult to do an in-place upgrade. The later MSI with more fixes are available with the CSS and but not on download link.
    However, the SQL Compact that will ship with next version of Visual Studio, will have this fix and also the deployment story much better and cleaner.

    I will definitely get back to all of you for this and see if we can fix the deployment story earlier for you somehow.

    Keep sending in the feedback and issues you are facing. We're listening :)
    Member, SQL Server Compact. (Please hit Yes if this was the answer to your question) "The above post is "AS IS" without warranties and confers no rights."
    Wednesday, June 10, 2009 7:15 PM
  • I'm not entirely clear on the uninstall.

    Is it just the SQL CE 3.5 SP1 or do I also uninstall Design Tools and Query Tools?

    (After I've removed ExportSqlce first.)
    Thursday, August 13, 2009 12:25 AM
  • I proceeded as follows and it worked: Removed the scripting add on in Add/Remove Progs Removed the main SQL Server Compact 3.5 SP1 entry in Add/Remove (left the other two that I had) Installed the Hotfix, reinstalled the scripting add-on. All worked. Most of the assemblies after the install were ...0, with the exception of the Entities dll which was ...1
    Thursday, August 13, 2009 6:28 AM
  • To echo the concerns of a few of the other folks here we are stuck in a situation where we have a fix, but it is not easily deployable with a product.  In my personal situation we have a product which uses SQL CE 3.5 and the EntityFramework.  If my customer has installed SQL CE 3.5 SP1 prior to installing my product the only way for them to have the product work is to acquire and install the hotfix. 

    I have tried using local deployment in my app (all the hotfixed SQLCE dlls are being copied to the same folder as the application), but that doesn't seem to work either.


    bbalmer
    Thursday, August 20, 2009 8:00 PM
  • Since MS was not able to solve the problem in practicable way, I'm thinking about implementing an other database for the application.
    I'm thinking about using SQL Lite, for exemple (widely used and targets app-local databases), but there are many supported providers :

    http://msdn.microsoft.com/en-us/data/dd363565.aspx


    Steve
    Friday, August 21, 2009 9:39 AM
  • I am with you on this bbalmer. We have taken care of this in next release of SQL Server Compact with the upcoming release of Visual Studio. The setup glitches have been taken care of and you should have a smooth upgrade there on. You can verify that with the next beta release coming up.
    I will once again talk within team if we can do something to get the customers unblocked sooner than later.

    Regarding the private deployment, it should work fine, did you make a right app.config entry with your application? Because the Entity dll for compact gets loaded through the ProviderFactory model you should add an entry for the same in app.config.

    Refer: http://blogs.msdn.com/stevelasker/archive/2008/10/22/privately-deploying-sql-server-compact-with-the-ado-net-entity-provider.aspx

    Let me know if this does not work for you, I would be happy to help (email id below).

    Ravi
    [ravi(dot)tandon(at)microsoft(dot)com]
    Member, SQL Server Compact. (Please hit Yes if this was the answer to your question) "The above post is "AS IS" without warranties and confers no rights."
    Friday, August 21, 2009 10:03 AM
  • Hi Ravi-
    Thank you for getting back to me.  Yes - I have followed the steps in your reference Url, and they work fine if the target machine has a clean environment, but if they aleady have installed SQLCE 3.5 SP1 then the local assemblies (hotfixed) get ignored and assembly probing causes the ones in the GAC to be used.  My development machine in 64-bit, but i have tried all variations of forcing the project into 32-bit mode, or allowing it to go to Any-CPU. 

    I am thinking that for now I am going to do as Steve suggested and investigate SQL Lite as I have an immediate need.  I actually had their page up in the browser when I got the note:>

    Brett
    bbalmer
    Friday, August 21, 2009 2:03 PM
  • So i finally get around to using sql ce with entity framework, and my gawd this is ridiculous. Server gen keys slap me almost immediately, then this bug shows up 5 minutes later. I see this thread is over a year old now, and just this lame quick fix is still the only resolution for it? the sql ce entity framework needs a disclaimer on it: may cause excessive lack of productivity. come on guys, get a real SP out or something and fix this piece of garbage before I switch to sqllite forever.
    Sunday, September 06, 2009 7:31 PM
  • I tried using sql ce with entity framework, i said it gotta be the coolest thing and boost my productivity but... nope. I tried the hotfix, worked for me a little, but queries are not like with the sql server or what i was used with linq.

    a query like the one below


    var res = from d in list
                          where d.FirstName.ToLower().Contains(textBox1.Text) ||
                          d.Email.ToLower().Contains(textBox1.Text)
                          select d;


    usually brings all data if textbox1.Text string is empty but when i use entity framework with sql compact or even sql lite it does not bring any records.
    although, if i put just one letter, it starts getting data.
    • Edited by m.alexandru Thursday, September 10, 2009 7:15 PM mispelling
    Thursday, September 10, 2009 7:14 PM
  • i know its not your fault personally ravi.. but this is a frickin joke. we're supposed to wait for vs2010 to get this solved? surely you realize that you have lost all of us and many more as sql ce customers.. who would wait almost 3 years for a bug like this to be fixed? no one thats who. This is a trivial error! it works with regular sql server! surely the query processor is the same across the versions? they both generate T-sql for god sakes! surely your mighty TFS servers can do a source merge? we use svn and it sure can. and it doesnt cost 10000 bucks

    This is just microsoft ignoring their own customers and screwing them over as thanks for giving their new platform a chance. How can you do this to your own produt? how? i dont get it. are you for real trying to get us to choose other vendors? 

    what where you thinking, microsoft? at least it might probobly work perhaps in vs2010..

    Monday, September 28, 2009 10:06 PM
  • Greetings Everyone,

    SQL Server Compact SP2 Beta is out with Visual Studio 2010 Beta 2.
    Please go ahead and try it: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=411ba1c5-ba57-45b6-9148-91bed6e7a9f1

    In context of this thread this will alleviate these issues for you:
    1. Fixes the ntext error, or any EF queries that use String Parameters. (Hotifx - http://support.microsoft.com/kb/958478/EN-US, earlier released has been rolled up)
    2. Fixes the "AS" error. (Same Hotifx had this fix too and has been rolled up)
    3. This MSI will do the seamless upgrades. You just need to use this MSI for ClickOnce and any other scenarios. No Uninstalls anymore.
    4. We also got some reports of bad performance with EF designers if the Schema get's a bit complex and big. We have made changes for that, which should make the performance better.

    As always, we are open for feedback, suggestions, comments.

    Thanks a lot for using our product and the continued support!
    Cheers
    Ravi

    PS: You can also send your issues to us through http://blogs.msdn.com/sqlservercompact/contact.aspx
    And as always I am here to assist you. Contact me for any SQLCE to EF issues: ravi[dot]tandon[at]microsoft[dot]com
    Member, SQL Server Compact. (Please hit Yes if this was the answer to your question) "The above post is "AS IS" without warranties and confers no rights."
    Wednesday, October 21, 2009 1:37 PM
  • I tried using sql ce with entity framework, i said it gotta be the coolest thing and boost my productivity but... nope. I tried the hotfix, worked for me a little, but queries are not like with the sql server or what i was used with linq.

    a query like the one below


    var res = from d in list
                          where d.FirstName.ToLower().Contains(textBox1.Text) ||
                          d.Email.ToLower().Contains(textBox1.Text)
                          select d;


    usually brings all data if textbox1.Text string is empty but when i use entity framework with sql compact or even sql lite it does not bring any records.
    although, if i put just one letter, it starts getting data.

    m.alexandru,

    Can you please send me your application at ravi[dot]tandon[at]microsoft.com
    We will definitely try to look into this and investigate on why is this not working against Compact.

    Thanks
    Ravi
    Member, SQL Server Compact. (Please hit Yes if this was the answer to your question) "The above post is "AS IS" without warranties and confers no rights."
    Wednesday, October 21, 2009 1:41 PM
  • ok i successfully downloaded the fix on my xp 32 bit laptop, but now on my 64 bit win7 laptop, i go request a download link and it never comes. Any ideas guys?
    Tuesday, October 27, 2009 6:32 AM
  • Suggest you use 3.5 SP2 (currently in beta), it also includes a x64 version: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=411ba1c5-ba57-45b6-9148-91bed6e7a9f1#tm
    http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.
    Tuesday, October 27, 2009 7:31 AM
  • Useful information for me.
    Sorry Seems to Be The Hardest Word... money for surveys
    Monday, November 09, 2009 11:47 AM
  • 4. We also got some reports of bad performance with EF designers if the Schema get's a bit complex and big. We have made changes for that, which should make the performance better.
    My bad about this point. The change is not in Beta 2, however you will get the change in RTM. If there is some volunteer who can help me get more real-life data/schema which is giving bad performance, it will really help me in the testing the fix better and perhaps look for more improvements in this area.

    Thanks! & Sorry :(
    Member, SQL Server Compact. (Please hit Yes if this was the answer to your question) "The above post is "AS IS" without warranties and confers no rights."
    Monday, November 09, 2009 12:15 PM
  • SQL CE 3.5 SP2 went live a few days ago, for those of you who are just running into this issue.
    Thursday, April 15, 2010 11:13 PM
  • We updated recently to 3.5 SP2 cumulative update 5 (3.5.8087), and are seeing this issue. Does anyone know if Microsoft has a new fix for this?
    Monday, November 28, 2011 6:27 PM