LINQ to SQL Forum

Error SUMming values that include null values as well

Wed, 25 Nov 2009 21:47:29 Z

I am having problem with following query:

Here object P contains Q. However, Q may be missing for some P. Q has an Amount attribute. I am trying to sum that amount in Q belonging at the level of P.
Since Q.amount is numeric, PTotal is also of numeric type and it does not accept null values and I get an error. I tried to use the above expression but it does not work either and I get the same error. Can someone help me fix this?
Thanks

var aList = from P in DataContext.P

where P.Id == SelectedId

orderby P.Name ascending

select

new { PId = P.Id, Name = P.Name, PAmount = P.Amount, PTotal = P.Q.Sum(o => (o.Amount == null ? 0 : o.Amount)) };

Dynamic LINQ query error: Overload resolution failed ....

Mon, 23 Nov 2009 11:19:18 Z

I have some limited experience in the creation of normal LINQ to SQL queries but I now want to find a method of generating dynamic queries that will respond to user selections in various DropDownLists on my ASP.NET 3.5 web page. The following link to Scott Guthrie's blog article on dynamic LINQ queries looked like it would provide a potential solution:

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Unfortunately, I can't get dynamic LINQ queries to work. I have downloaded the VB Dynamic Query Library, copied Dynamic.vb into my ASP.NET site and referenced System.Linq.Dynamic in my VB.NET code behind page. However, my page fails with the compilation error message "Overload resolution failed because no accessible 'Where' can be called with these arguments" on the following line of code:

Recipients = myDBC.Entrants.Where("FirstName = 'David'")

I am probably making a very basic error somewhere. Any help would be much appreciated.

David

Commit single, specific entities or prevent specific entities from being committed

Wed, 25 Nov 2009 18:16:33 Z

Hi all,

We regularly come across situations where we need to insert/update only a specific, single object or objects regardless what else has changed and we cannot seem to do this in LINQ to SQL. Sounds simple enough but I'm completely stuck. Any ideas?

Regards,

Stephen

Linq To Sql XML fields don't update.

Wed, 25 Nov 2009 16:58:42 Z

Is their a work around for getting XML fields to Update when using a Linq datasource?
For reference see https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=360433&wa=wsignin1.0#tabs
which is a similar problem.

If I use a LinqDatasource and attach to the Updating event. The XML field does not get updated. The ProgramCode field does get updated.

protected void LinqDataSource1_Updating(object sender, LinqDataSourceUpdateEventArgs e)
        {
            Registration reg = (Registration)e.NewObject;
            reg.XMLData = new XElement("ContextData", Util.AuditHelper.GetContextData());
            reg.ProgramCode = "A";

LinqDataSource Select should give strong type

Wed, 25 Nov 2009 15:43:47 Z

I have a LinqDataSource, and in the select, I would Like to have something else than an anonymous type. I assume I would like to type something like this:

<asp:LinqDataSource TableName="Foo" Select="new KeyValuePair<int, string>(Id, Title)" ContextTypeName...

But it doesn't work. Ofcouse I could get an anoynous type with a Key and a Value, but it is just not the same.

Any good Ideas?

LINQ to SQL - 3 Tier Design

Tue, 24 Nov 2009 21:06:27 Z

Hi,

I've just started looking at LINQ to SQL and am very impressed with it, but I have a query about how it should fit into the data access layer of a 3 tier design.

It seems that LINQ to SQL is such a clean and concise technology that it has the potential to completely replace the DAL in an application as it does exactly the same job. Even if the DAL was to remain with LINQ to SQL as its internal workings, the DAL would be such a thin wrapper that it almost seems pointless.

Also, as the generated entity object have the ability to update the database simply by changing their properties, surely this renders the DAL useless.. Or at least provides a path for poorly written code to bypass the DAL?

It seems to me that the best way to use Linq to Sql is to scrap the DAL and allow the business logic layer to interact directly with the DataContext object, but this goes against every software design principle I can think of.

I guess im just a bit confused and looking for some best practice pointers.

Any help would be much appreciated.

Martyn.

Getting count of results without an additional query

Tue, 24 Nov 2009 15:38:22 Z

Hello! I'm trying to get number of results in collection that I've got with L2SQL but it causes a second database query.

In other words this code generates two separate SQL queries:

Model.Nodes = Nodes
                .Take<SideBarModel.NodeModel>(NodesPerPage)
                .ToList<SideBarModel.NodeModel>();

NodesNum = Nodes.Count<SideBarModel.NodeModel>();

How can I avoid this?

Complex Query

Tue, 24 Nov 2009 13:35:18 Z

Hi,
I'm quite new to Linq2SQL, and I need to write a query which I'm not sure how to do.
I have 2 tables: Persons and Emails. Naturally, there is more than one Email for every Person. However, every person has one (or zero) default email, designated by a "is_default" column in the Emails table.
What I want to do is this: Retrieve all the persons, with their default email address (single record per person). If the person has no default email, the query will return only his name, and leave the email empty.
How can that be done using Linq2SQL? Can I do that in a single query?

Thanks!

datagrivew small problem

Wed, 25 Nov 2009 08:22:12 Z

hi
i m new in vb
i have small problem with datagridview
i dont know whats parts im missing
lets say i have tow tables
1-items info
2-sales invoice
what i need when i type the items number in datagridview cell 0 (datagridview that belong to invoice tables)
its bring the data for the item like item price,name...etc
i tried in cellendedit
but i dont know the right way to do it
i dont want to use textbox ,and if there is anything better to use it than datagridview please let me know
any help ?
thanks for every one

Why am I getting an error on DeleteAllOnSubmit while trying to delete a record from a database?

Thu, 19 Nov 2009 20:11:11 Z

Hi,

I have two tables with the following fields:

Assets	HistoricalPrices
AssetID	Date
Ticker	AssetID
Name	OpenPrice
…	…

I have a 1-to-many relationship between the table Assets and the table HistoricalPrices. Because of this, in order to delete an asset I first need to delete all the historical prices of that asset.

I get the following InvalidOperationException error “Cannot remove an entity that has not been attached.” on the DeleteAllOnSubmit line of code. This is my code:

TTSDatabaseDataContext db = new TTSDatabaseDataContext();

Asset asset = new Asset();

// Get asset information from the ComboBox which contains asset items

asset = (Asset)ticker.SelectedItem;

// Delete the historical prices of the asset to delete

db.HistoricalPrices.DeleteAllOnSubmit(asset.HistoricalPrices);

// Delete the asset

db.Assets.DeleteOnSubmit(asset);

db.SubmitChanges();

What is incorrect in my code? I’m using an example very similar to one that I found on the book “Pro LINQ Language Integrated Query in C# 2008”.

I’m developing an application using Visual C# 2008 Express Edition and SQL Server Express Edition.

Thanks.

Best regards,

Miguel.

Its complicated even for a title

Wed, 25 Nov 2009 01:05:54 Z

Hi all,

Here is the scene.....
I 2 separate contexts in LINQ, the actual database is one, and the contexts basicaly break concerns, and in this case, I using 3 tables.

Table 1 (AccountId) (Context 1)

Table 2 (PersonId) (Context 2)

Table 3 (AccountId, PersonId) (Context 2)

So what happens here is that (Context 2) can only create a record on Table 3 after a record has been created in Table 1.

With that in mind, I created a ActionScheduler, that takes all 3 Linq to SQL objects, and creates watchers on the properties that matter (AccountId, PersonId), and when both of them are not the default values ( 0 ), meanind that they have being inserted, the 3rd one (Table 3) object is then assined the AccountId, and added to Table 2s list of Table 3 objects.

So what I tecnically would have to do is basically call SubmitChanges 2 times and things would be good.

Well thats exactly when things fall apart. I make the first call, Table 1, gets inserted triggering the event on the ActionScheduler, that then updates Table 3 object, then Table 2 get inserted triggering Table 3 object, which now meet the requirement (Both PersonId, AccountId are valid), Table 3 object is added to Table 2 object.
The second save is called, and there is no changes here , so nothing gets added at all, now if while having the same contexts open I add another set of objects, and save all over again, the new Table 3 object does not get added (same behavior) but the other one does with perfect values, so it seem like this guys is always one step behind.

Any sugestions? Is there a better way of doing this, how exaclty is the SubmitChanges lifecycle?

Sorry for all the questions, but this is really bugging me. And an obvious thanks.

Binding you through life - Se serve, marque!!!

Cannot add an entity with a key that is already in use.

Fri, 20 Nov 2009 15:32:46 Z

Hello,

I am creating two Assets using LinqToSQL as follows:

      ISession session = new Context();
      AssetRepository assetRepository = new AssetRepository(session);
      assetRepository.Create(new Asset { Id = 1, Mime = new Mime { Id = 9 }});
      assetRepository.Create(new Asset { Id = 2, Mime = new Mime { Id = 9 }});
      session.Commit();

Session implements Unit of Work pattern:

public interface ISession {
    void Commit();
    void Rollback();
} // ISession

public partial class Context : DataContext, ISession {
    public void Commit() {
      SubmitChanges();
    } // Commit
    public void Rollback() {
      Dispose();
    } // Rollback
} // Context

What ever I do I always get the following error:
"Cannot add an entity with a key that is already in use."

On code line:
_context.Mimes.Attach(mime);

On my repository create asset code:
    public void Create(AssetModel asset) {

      // Define entity
      Asset _asset = new Asset { Id = asset.Id};
      _context.Assets.InsertOnSubmit(_asset);

      if (asset.Mime != null) {
        Mime mime = new Mime { Id = asset.Mime.Id };
        _context.Mimes.Attach(mime);
        _asset.Mime = mime;
      }

    } // Create

Basically, in each Asset there is a MimeId.

Anyway, is there a way to overcome this?
I am really not sure what am I doing wrong.

Here is the SQL code for Assets and Mimes:

create table dbo.Assets
(
Id int not null,
MimeId int not null,
    constraint PK_Assets primary key clustered(Id)
) -- Assets

create table dbo.Mimes
(
Id int not null,
Type nvarchar(80) not null,
    constraint PK_Mimes primary key clustered(Id)
) -- Mimes

alter table dbo.Assets
add constraint FK_Assets_Mimes foreign key(MimeId) references dbo.Mimes(Id) on delete no action on update cascade;

Note: Assets table has more columns, like Content, etc ...
           But for sake of simplicity I am not including this on my code as I am just testing the relations.

Could someone tell me how should I solve this?

Thanks,
Miguel

How Do I Avoid This Behavior?

Fri, 20 Nov 2009 19:24:56 Z

Say I have an entity that looks as follows

public Order OrderEntity {
EntityRef CustomerEntity;
EntitySet OrderDetailEntity;
...
...
}

When I save a OrderEntity, L2S, will want to also save the entity in CustomerEntity and all the entities in OrderDetailEntity. Sometimes we want this behavior and sometimes we do not. For those times we do not, how to tell L2S not to do this?

Thanks - Randy

Repeated assignments overwrite a field in query

Tue, 24 Nov 2009 15:45:19 Z

Hi all,

I'm having this weird issue, look at my code:

var query = from rud in db.DBRud select rud;

foreach(myObject p in objects)
{
    switch(p.type)
    {
        case "rudobject":
           //p.code now is "linea_di_prodotti"
           query = from r in query  
              join s in db.ValueRudobject on r.id equals s.rud_id
              where s.field_code == p.code && values.Contains(s.value) select r;
         break;
      
        case "checkbox":
            //p.code now is "us"
            query = from r in query
               join c in db.ValueCheckbox on r.id equals c.rud_id
               where c.field_code == p.code && values.Contains(c.value) select r;
        break;

    }
}

so, the query is built dynamically. But if you look at the sql produced, you'll notice that the second assignment overwrites the variable p.code! Look:

SELECT [t0].[id], [t0].[user] AS [user], [t0].[name], [t0].[status], [t0].[notes], [t0].[rudobject], [t0].[internal_name], [t0].[visible], [t0].[created], [t0].[modified], [t0].[locale], [t0].[metadescription], [t0].[keywords], [t0].[referer], [t0].[folder], [t0].[position]
FROM [dbo].[ruds] AS [t0]
INNER JOIN [dbo].[ruds_values_checkbox] AS [t1] ON [t0].[id] = [t1].[rud_id]
INNER JOIN [dbo].[ruds_values_rudobject] AS [t2] ON [t0].[id] = [t2].[rud_id]
WHERE
    ([t2].[field_code] = @p0) AND
    ([t2].[value] IN (@p1, @p2)) AND
    ([t1].[field_code] = @p3) AND
    ([t1].[value] IN (@p4)) AND
    ([t0].[locale] = @p5) AND
    ([t0].[rudobject] = @p6)
  
  
    -- @p0: Input VarChar (Size = 17; Prec = 0; Scale = 0) [linea_di_prodotto]
    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [42]
    -- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [43]
    -- @p3: Input NVarChar (Size = 17; Prec = 0; Scale = 0) [linea_di_prodotto]  ERROR !!! should be "us" !!!!!!!!!!
    -- @p4: Input NVarChar (Size = 13; Prec = 0; Scale = 0) [International]
    -- @p5: Input VarChar (Size = 5; Prec = 0; Scale = 0) [it_IT]
    -- @p6: Input VarChar (Size = 8; Prec = 0; Scale = 0) [prodotto]
    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

It's for sure a question of variables overwriting, because if I change the order of the items inside the variable objects (the one into the foreach) I got "us " duplicated, instead of "linea_di_prodotto ". So, the last value in p.code overwrites the value of all the other previous p.code.

What do you think about? Do you have any suggestion for a workaround?

thanks
darko

Take value based upon textbox value

Tue, 24 Nov 2009 10:35:47 Z

Hello all, I am creating a gridview that will be populated based upon a linq statement, the sql is as follows:

SELECT     TOP 10 IDDesc, UnitUserfield1, UnitUserfield2, ProductPercentage
FROM         tblOnlineReportingCOMPLETEWeights
WHERE     (MaterialLevel = 'Primary') AND (MaterialText = 'Paper')
ORDER BY ProductPercentage DESC

Now, what I would like to do is let the user specify the Top 10, so essentially it is a "Top x" this being defined in a textbox i.e. they type in 50 into the textbox, the linq query is executed and the gridview displays the top 50.

I understand that using Take is the area I want to look at, is this correct? Is this even possible?!

Any thoughts, muchly appreciated.

PS: apologies for asking thick questions, I am very new to all of this!

writing a linq query to extract data from two data sources

Tue, 24 Nov 2009 11:34:44 Z

Can we write a single line of linq query for extracting data from two differnet data sources
i.e data source 1: a list
data source 2 : an XML file

Distinct Query

Tue, 24 Nov 2009 10:29:59 Z

Hello all.

I have the following LINQ statement that appears to be working fine and dandy. Hwoever, someone has pointed out that my distinct may not be doing exactly what I want it to do (i.e. essentially group the UnitUserfield1).

Can someone explain the logic behind this and point me in the right direction as to how to amend this? The code is as follows:

 var query = (from r in db.tblOnlineReportingFINALPRODUCTs
                         where r.MemberId.Equals("FM00012")
                         select new { unitUserfield1 = r.UnitUserfield1 }).Distinct().OrderBy(r => r.unitUserfield1);

linq2 sql executeQuery and database load options issue.

Thu, 19 Nov 2009 13:02:43 Z

Hello,
I'm facing with the next situation:
former- i've to send a direct query to database engine because of sql server in clause parameter limitation.The code is :

string tmp = string.Join(",", (from x in ids select x.ToString()).ToArray());

var xx = bl.DataContext.ExecuteQuery<Element>(" select * from Elements as a where a.ElementID in (" + tmp + ")").Where(x => x.Questions.Count > 0);

latter- even though i've database load option setted in my datacontext : mdatacontext.LoadOptions = shape;

var

shape = new DataLoadOptions();

shape.LoadWith<

Element>(x => x.Questions);

shape.LoadWith<

Section>(x => x.Questions);

mdatacontext.LoadOptions = shape;

The linq2sql engine seems to igonore them and when i'm executing this query a query is generated for every parent item -i mean element object.
Can you help me to sort this out please?

Thank you!

Why is ToLookup() dependent on load options in Linq2Sql?

Thu, 19 Nov 2009 14:42:46 Z

I recently stumbled upon a problem in Linq2SQL that I explain below.

Lets say I have 3 tables Posts, PostTags and Tags defining a many-to-many relationship. I want to get a lookup table that will give me all the Posts related to a given tag so I use the following code:

return
 dataContext.PostTags.ToLookup(pt => pt.Tag, pt => pt.Post);

In unit test all went fine but in the real application, it didn't work. I found out that I had different load options between my unit test and my application.

When dataContext.DeferredLoadingEnabled = true; , everything is fine and works as expected but when dataContext.DeferredLoadingEnabled = false; and you don't have the load options from PostTags to Post and Tag , the lookup returned contains a single key (null) with an empty array for value. The generated SQL is

SELECT

 [t0].[PostID], [t0].[TagID]

FROM

 [dbo].[PostTags] AS
 [t0]

So when it generates the lookup, pt => pt.Post returns null and the same goes for Tags.

I found a way to make it work no matter what the DeferredLoadingEnabled property value is and whatever the loadoptions are:

var
 lookup = (from
 pt in
 dataContext.PostTags
              select
 new
 {pt.Post, pt.Tag})

             .ToLookup(x => x.Tag, x => x.Post);

Why can't the Linq2SQL provider generate the right SQL in the original case and return me a meaningful lookup? By the right SQL, any SQL that would return the right Post and Tag objects and allow for grouping them correctly.

(As a cross-reference, here is my question on stakoverflow.com http://stackoverflow.com/questions/1724748/why-is-tolookup-dependent-on-load-options-in-linq2sql)

How can the DateTime format be customised when storing in varchar

Sun, 22 Nov 2009 23:21:12 Z

I use an SQL varchar field to store my datetimes and would like Linq-to-SQL to store DateTime fields using ToString("o").


[Column(Name = "inserted", DbType = "varchar(30)", CanBeNull = false)]

 public DateTime Inserted { get; set; }

A Linq-to-SQL insert will result in a format of "mm/dd/yyyy HH:mm:ss". How can I specify that the datetime string format used when storing in the database should be ToString("o")?

Thanks.

Aaron

Incorrect Query Result Set Returned

Tue, 17 Nov 2009 19:05:03 Z

I have a very complex Linq to SQL query that returns a result set from a Microsoft SQL Server database. The query is created using syntax similar to:

Dim db as MyDataContext = MyGetDataContextHelper()
Dim qry = From rslt in db.MyView Select ColumnList

If userParam1 IsNot Nothing Then
    qry = qry.Where(lambda for the filter)
End If

etc....

qry.ToList()

There are several user-specified filters to the query, including one that does a geographic radius search.

Here's the problem. I have a break set on the "ToList" call right at the end. When the break is hit I use the Linq to SQL Debug Visualizer to see the generated SQL statement. I copy that complex SQL statement into a SQL Server Management Studio query window and execute it against my database to get exactly the result set I want. So the generated SQL appears to produce the desired result. However, when I execute the "ToList" method of the query object the list returned has fewer rows and some different rows.

How can that be? If the generated SQL is simply passed over the connection to the SQL Server shouldn't it generate exactly the result set I see in SQL Server Management Studio? I assume that I am misunderstanding something about the Linq to SQL mechanism, i.e. that it's not just a passthrough to SQL Server. Is that correct?

Thanks in advance,

Bob Mc.

New row in a cascaded DataGridView

Fri, 20 Nov 2009 21:28:17 Z

Refering to Beth Massi's blog on the subtlties of cascading datagridViews: -
http://blogs.msdn.com/bethmassi/archive/2007/10/02/linq-to-sql-and-one-to-many-relationships.aspx?CommentPosted=true#commentmessage

I have Customer and Order DataGridViews with the DGVOrder datasource being based on customer.

I am having trouble adding a new Order if the Customer doesn't already have an order assigned to it.

The problem is caused by the fact that Order.CustomerID is only auto generated when Order table has data in it.

Could anyone tell me how to get the Order.CustomerID to auto populate correctly, and elegantly, when there are no entries in the Order table?

My binding code is as follows: -

DC = New
 DataClassesWhatToPlantDataContext

DC.Log = writer

CustomerList = (From
 s In
 DC.Customer Order
 By
 s.Name)

CustomerBindingSource.DataSource = CustomerList
CustomerDataGridView.Datasource = CustomerBindingSource

OrdersDataGridView.DataSource = CustomerBindingSource
OrdersDataGridView.DataMember = "Orders"

Many Thanks

Paul

Converting from SQL to LINQ

Thu, 19 Nov 2009 13:38:28 Z

Hi,
I need some help translating this query to LINQ.

SELECT

[t1].[Description] As [ModelNumber],
        (SELECT [t2].[Value] FROM [dbo].[Client_ProductValues] AS [t2]
         INNER JOIN [dbo].[Client_ProductAttributes] AS [p0]
         ON [p0].[ClientProductAttributeID] = [t2].[ClientProductAttributeID]
         WHERE ([p0].[Field_Mapping] = 'Product_Name') AND ([t1].[ClientProductID] = [t2].[ClientProductID])
        ) AS [DisplayName],
        (SELECT [t2].[Value] FROM [dbo].[Client_ProductValues] AS [t2]
         INNER JOIN [dbo].[Client_ProductAttributes] AS [p0]
         ON [p0].[ClientProductAttributeID] = [t2].[ClientProductAttributeID]
         WHERE ([p0].[Field_Mapping] = 'Model_Series_Name') AND ([t1].[ClientProductID] = [t2].[ClientProductID])
        ) AS [DisplayName]
FROM
        (SELECT [t0].[ClientProductID], [t0].[Description], [t0].[OrgID]
          FROM [dbo].[Client_Products] AS [t0]
          GROUP BY [t0].[ClientProductID], [t0].[Description], [t0].[OrgID]
        ) AS [t1]

Your help is always appreciated.

thanks,

kashif

Type or namespace name not found

Thu, 19 Nov 2009 23:13:12 Z

I am using the sample code in: How to: Connect to a Database (LINQ to SQL)

I am able to compile without errors except the following compile error:

error CS0246: The type or namespace name 'Customers' could not be found (are you missing a using directive or an assembly reference?)

The error is for the line:

public Table<Customers>;

I agree with the compiler; I don't see how it knows what the Customers table is. I get that error when I use the sample code that uses the Northwind class but I also get the error if I don't use the class and use code that uses GetTable.

I apologize for asking such a simple question; I assume there is a simple answer and I need to read all about LINQ and all of that. If someone does not mind answering this question, that will help.

Sam Hobbs; see my SimpleSamples.Info

'System.Web.HttpUnhandledException' was thrown

Wed, 18 Nov 2009 11:41:45 Z

I'm facing an unknown issue, I developed an application which is working fine on local development machine but unfortunately it is fail to function. It is consume data using LINQtoSQL from two different databases, both databases are hosted on different servers. Running application on local machine gives good performance as well good result as our desire, any help will be appreciate and thanks in advance.

Following Error I'm getting:

Error - Data

System.Collections.ListDictionaryInternal

Error - Message

Exception of type 'System.Web.HttpUnhandledException' was thrown.

Error - Source

System.Web

Error - Stack

Trace at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.clients_testre_aspx.ProcessRequest(HttpContext context) in c:\windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\resources\9dcb6504\2680bc\App_Web_ceq8vnnp.4.cs:line 0 at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Regards

Salman Farsi

ling large table

Wed, 11 Nov 2009 18:26:08 Z

Hi,

Im facing the problem described below.

I use linq to proceed each record from table. Table contains 130 000 000 records so its quite huge. Yes I know about Integration services but I need to use winforms for that. And I like LINQ.

Ok the problem is that proceessing such a big numer of records and storing results in memory can make lack of memory problem. So that I would like to be able to proceed for example 1000 records than other day proceed another 1000 records and so on.

Problem Is that on such a huge table making object.skip(1000).take(1000) linq statement gives me a timeout excpetion.

Any ideas why is it like that ?

thanks for help

Unsespected behavior need help plz.

Thu, 19 Nov 2009 11:26:32 Z

I get the following error

Cannot insert the value NULL into column 'ProjectID', table 'dbProPlan.dbo.ProjectFase'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Wich in sense is not strange if the value would be NULL but it aint.

I use the following code

IEnumerable<KHKVKBelege> orderList = from order in DbKHK.KHKVKBeleges where order.Belegnummer.ToString().StartsWith("19") orderby order.Belegnummer ascending select order;

                foreach (KHKVKBelege order in orderList)
                {
                    try
                    {
                        Project project = new Project() { ID = order.Belegnummer.ToString() };
                        if (!DbProPlan.Projects.AsEnumerable().Contains(project, new LambdaComparer<Project>((x, y) => x.ID.Equals(y.ID))))
                        {
                            project.StartDate = (DateTime)order.Belegdatum;
                            project.CustomerID = order.A0Empfaenger.Trim();
                            ProjectFase fase = new ProjectFase() { Name = "Fase 1" };
                            IEnumerable<KHKVKBelegePositionen> regels = from details in DbKHK.KHKVKBelegePositionens where details.BelID.Equals(order.BelID) select details;
                            foreach (KHKVKBelegePositionen row in regels)
                            {
                                if (row.Artikelnummer != null)
                                {
                                    ProjectPart projectPart = DbProPlan.ProjectParts.Single(part => part.ID.Equals(row.Artikelnummer.Trim()));
                                    ProjectItem item = null;
                                    switch (projectPart.Category)
                                    {
                                        case ProjectPartCategorys.Consultancy: item = new ProjectItemConsultancy() { NumberOfDays = Convert.ToInt32(row.Menge) }; break;
                                        case ProjectPartCategorys.Training: item = new ProjectItemTraining() { Attendees = Convert.ToInt32(row.Menge) }; break;
                                        case ProjectPartCategorys.Software: item = new ProjectItemSoftware() { Licences = Convert.ToInt32(row.Menge) }; break;
                                        default: item = new ProjectItemConsultancy() { NumberOfDays = Convert.ToInt32(row.Menge) }; break;
                                    }
                                    item.ProjectPart = projectPart;
                                    fase.ProjectItems.Add(item);
                                }
                            }
                            project.ProjectFases.Add(fase);
                            DbProPlan.Projects.InsertOnSubmit(project);
                            DbProPlan.SubmitChanges();
                        }
                    }
                    catch (Exception e)
                    {
                        LogWriter.WriteLine(LogTypes.Error, string.Format("Synchronization of project with id {1} failed; {0}", e.Message, order.Belegnummer.ToString()));
                    }
                }

As you can see the project number is atleast '19' after that I create a fase object and add it to the collection of fases of the project. This is all fine and good. If I check the fase object it even gets the number of the project (Thanks to link to sql it does it automaticly) At the ProjectID then I add the projects to the Projects table and sumbit the changes. And I get the error, why does the ProjectID disappear from my ProjectFase?

Existing rows not update when I execute the query again

Wed, 18 Nov 2009 10:24:22 Z

Hi,

I've a readonly DataGridView on a form,
this DataGridView should update every <n> seconds.

at form level I've declared:
MSGDataContext Db = new MSGDataContext();

In the Load event of the form I use a code like this:

Db.Log = Console.Out;
var myStatus = Db.ExecuteQuery<STATO_INXINVINI>(SqlSelect);
STATO_INXINVINIBindingSource.DataSource = myStatus;

Insiede a timer I execute the query again and again but I've noticed that existings rows are NOT UPDATED with new values readed from the db. New rows instead are correctly added... looking at the Log the Select statement is executed every time.

Is it possible to update existings rows without instancing a new DataContext before calling again the select ?

I've noticed that there's a way to update the rows calling the resetbinding on the bindingsource but it executes a new select for each row so it's slow.

Similar code without LINQ (using a DataTable) works correctly (it updates the rows on the grid) but I've already defined a datacomtext including the STATO table for other purposes and I prefer not to use a dataset only to solve this problem.

Thank you very much every help is appreciated.

SqlGeography and LINQ to SQL

Wed, 18 Nov 2009 22:31:34 Z

There was a post a while back that suggested that the spatial types might be added to a future release of LINQ to SQL and Entities, but I'm not seeing any love in the VS 2010 Beta 2 bits. I've got a big enough project going right now using geography types that this would really save my rump. I can influence the data model, but for the most part it's already done. We're using LtoS extensively and I'm hoping I can start using tables with those types in the wicked cool designer we get in VS.

Does anyone have any news?

Thanks in advance!

Cheers,

-jc

Me, coding and stuff: Mr. James

64-bit problem

Wed, 01 Jul 2009 23:40:28 Z

We are currently getting a problem on Vista 64 where certain LINQ To SQL queries lock up the machine, with 100% CPU usage. This is in an ASP.NET application. If we switch the application over to running as a 32-bit app (rather than 64) then the problem goes away.

Are there any known bugs in this regard? Any hotfixes available or planned?

John

John Rusk, http://dotnet.AgileKiwi.com - .NET and Agility

Adding Child entities yields an exception ... :-s

Wed, 18 Nov 2009 22:38:51 Z

I am new to C# and LINQ to SQL ... I have the following entity relationships ...

DbCollaboration
+---DbSchedule
+---DbOccurrence

I am dumping data gathered from the web in xml for with a class hierarchy like this:

WbxEvent
+---WbxConference
+---WbxOccurrence

The two models emulate each other, the Linq objects are those beginning with Db.

I have the following code to TRY to attempt to create the objects in the Db* Entities, however I get an "Object is not set to an object" exception at the indicated line:

foreach (WbxEvent e in webexEvents)

//set the collaboration data from the webex event

DbCollaboration cb = new DbCollaboration(e);

//get the schedules

if (e.Conference != null)

DbSchedule s = new DbSchedule(e.Conference);

//get the occurrences

foreach (WbxConferenceHistory h in e.History)

DbOccurrence o = new DbOccurrence(h);

cb.DbOccurrences.Add(o); //<<<-- Exception is raised here (note, the "get conference" portion was skipped)

}

}

Any help would be appreciated. I am unsure what would be the most helpful for evaluation of my issues, so if you need additional info, don't hesitate to ask and I will provide that information.

The Ex.Message is:
Object reference not set to an instance of an object.

{

s.DbCollaboration = cb;

cb.DbSchedules.Add(s);

}

{

//get the web ex stuff into the database

Brent Spaulding | Access MVP

Help!

Wed, 18 Nov 2009 10:31:24 Z

Hi i need some help, but i post it on the wrong section that's why i will post here the link

http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/94778e18-69f7-45db-8d73-0bfd68508b76

http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/e1831258-0841-4fd0-9c4e-e732ae32aab2

ident_current

Mon, 16 Nov 2009 08:37:44 Z

hi friends
i wanna retrieve last identity in SQL table with linq,in SQL i use ident_current..how can i have this in LINQ ?

Nested Query

Mon, 16 Nov 2009 08:02:57 Z

SELECT * FROM ClientTbl AS C
WHERE C.ID IN ( SELECT UserID FROM ClientSetTbl WHERE ID = 123 )

How can I convert the above to LINQ? I have tried many suggestions in the web including converting the UserID into a List before the query, but unable to figure out the correct way of doing.

Note: The above code is extracted from a very long query. I can't change the way it works.

Many thanks in advance :)

Using C# method in where clause or join

Mon, 15 Jun 2009 12:43:52 Z

Hi

I am writing a generic framework and need some help.

I have a method that on a Table<TEntity> select entries. The challenge is that the entries selected are depedent on other entries in a global changelog table. In this changelog entries are identified in two columns, one with the tablename, and one with the key columns in the following format, e.g [ID:123] or if multiple keycolumns exist [ID:1234][OtherID:hest]

So by examining the ColumnAttributes on the properties for a table class, i can make this "rowkey". All properties with columnAttribute and the IsPrimaryKey set to true is in the key.

Now, when i try to something like this:

Table<MyTableClass> table = myDataContext.GetTable<MyTableClass>();

var rows = from row in table where CreateRowKey(row) == "[ID:1234]" select row;

I get an System.NotSupportedException. Method CreateRowKey has no supported translation to SQL.

My question is now:
Are there some way i can make this method, so it translates to SQL in can be used. The result from the function could be something like '[ID:' + row.ID + ']' that could be inserted into the TSQL generated by LINQ.

I am new to LINQ, but the "framework" generated expressions that are evaluated to SQL, can't i input some custom SQL as well? Or do i need to generate the entire SQL and use ExecuteCommand?

I hope you can figure out what im trying to do, and can provide me with some info on where to go. (Can System.Linq.Expressions solve this?)

/Anders

LINQ Where Clause for UI

Tue, 17 Nov 2009 22:11:26 Z

I have a UI that has some date fields used to specify date ranges for results as well as a few text boxes to filter results like the contents entered in the boxes. Obviously, these fields aren't always filled in, so my where clause can have various filtering needs depending on what the user enters. Does anyone have any examples of handling this in LINQ? I use dynamic sql in other older apps but this app is already using LINQ with SQL so I was wondering if that could handle this or not.

Thanks,
Adam

LINQ multiple where clause

Tue, 17 Nov 2009 13:16:53 Z

Hello all...thicky MrDean here!!!

I have the following code that build a web method that is ultimately used in the good old autocompleteextender

TORDataContext db = new TORDataContext();

            return db.tblReporting

                .Where(r =&gt; r.UnitUserfield1.Contains(prefixText)).OrderBy(r =&gt; r.UnitUserfield1).Select(r =&gt; r.UnitUserfield1).Take(count).Distinct().ToArray();

Now, I would like to add in another where clause into this i.e. to select a certain member within the tblReporting. I have attempted the following with no success:

return db.tblReporting

                .Where(r =&gt; r.MemberId=("123") &&(r =&gt; r.UnitUserfield1.Contains(prefixText)).OrderBy(r =&gt; r.UnitUserfield1).Select(r =&gt; r.UnitUserfield1).Take(count).Distinct().ToArray();

Can someone point out the error in my ways - apologies for what is a simple question, I am brand spanking new to this development malarky!

Getting the ID from a table using LINQ to SQL?

Tue, 17 Nov 2009 11:21:52 Z

The folowing procedure is suposed to get the ID from the Author table, but I get the error, which is shown below the code snippet. This worked when I used an int instead of string, but with string it doesn't work. I have to use string, because the AuthorID is generated using text.

public void GetAuthorID()
        {
            string[] myList = new string[10];

            var query = from au in myDb.Author
                        where au.AuthorName == mAuthorName
                        select au.AuthorID;

            myList = query.ToArray;

            mAuthorID = myList[0];
        }//End proc

Cannot convert method group 'ToArray' to non-delegate type 'string[]'. Did you intend to invoke the method? E:\Visual Studio Projects\SILibraryProject\Application\SmalLib\BusinessLogic\BusinessLogic\clsAuthorBL.cs 91 22 SmalLibBL

Search in the If s

Tue, 10 Nov 2009 17:02:40 Z

Hi Friends,
Search engine to use SQL working.Linq to my query.
Search engine also different search options to filter yapıyor.Dolaysıyla search engine olacak.Benim (picture ) 2 one DropDownList, 4 RadioButton, TextBox and the TextBox in the keyword input have Ajax caledarler.
This problem friends,
Users will try different combinations on different search engines. (For example, only the category list selected keyword was entered and the city searching for a combination of these ...)
I like these control conditions and to provide if working.Dolaysıyla is too long. (Code here )
I'm looking to remedy this, friends.
Waiting for your ideas.
Goodbye ....

DataBinding - Please help me!! i already posted this and nobody answered

Thu, 12 Nov 2009 09:35:18 Z

hi
i have a windows application.
i have a user interface which present data from database, create data from user input that will be added to databse through the application etc'.
i have 2 question:
1) i know that in ADO.net i can use dataset and define to the dataset insert, update and delete statement. i can display values with the dataset on grid, i can allow user edit the values and they will be updated on database with no effort by my side. is there a way to do something like that with linq? i know that i can bind table to grid, for example, as the data source but i couldnt succedd binding 2 tables with relations (one has a foreign key from the other) on grid. is there a way to do this? how can i use tables with relation and display the relation?
in addition i tried binding one table to grid, edit values and than use submit changes function but the data in database is not being updated. is there a way doing that? again, its simple in ado.net but i couldnt find it in linq so i do all of the work by myself
2) i have a stored procedure which retrieve data from some tables. i display it on grid. user do actions on that and create an entity which i need to insert to database. what is the correct way to work in such case? i am creating my own object of type that is very much like the dbml table (or the database table) that i want to insert the data into. after ceating the object and populating it i transfer the object to the data context layer, create object of the database table type, populate it with the data from the object and insert to database - this is a lot of work. i work like that because it seems to me incorrect to work with tables type of the database in a higher level than the data context layer. is this conccept correct? what is the best way to work in the described example?