|
|
September 2008 - Posts
-
I'm using XmlSerializer to serialize my LINQ to SQL objects to XML, which works great. The default behavior seems to be to serialize column fields into the xml as child nodes. I want to have some of elements to be attributes on the parent node, rather than being child nodes. I'd also like to control the order in which some elements appear in the XML. Does anyone know how I can do this with a LINQ to SQL object? I'm pretty sure I could move things around after the serialization is done, but can I Read More...
|
-
I have a large parent child hierarchy in a table called "Nodes". I wanted LINQ to load this entire table into memory to increase performance. So I got creative and ran a query where the predicate is true for each row, something like var results = from n in Nodes where n.ID > 0 select n; The Problem is that LINQ begins querying the database one parent at a time. It takes hours to complete this query. I want LINQ to be smart, take the entire table all at once and build the parent child after receiving Read More...
|
-
LINQ is a great tool for writing ad-hoc queries and transforms, and occasionally I need to write queries or transforms on text files. And sometimes I receive CSV files, and need to do something with them. I wrote a blog post on LINQ to Text files over two years ago. My coding practices today differ from what I presented in that blog post. This post presents my current approach for dealing with text files using LINQ, and includes a function for splitting CSV lines. In that post, I detailed an approach Read More...
|
-
Hi, Imagine the following scenario : in one hand, I have a corporate system that stores data in a proprietary format. We have set a c# 2.0 API that can query the store. in the other hand, I want to build a new GUI for accessing data over the network (certainly using WCF). As the GUI is providing dynamic filters, I have thinking of creating a custom linq provider to access our store, and then that allow to chain query and access data only when enumerating results (like linq to sql). Since I want to Read More...
|
-
Sorry for such a broad range of things in the title, but I am just getting into LinqToSQL and am trying to understand the concepts in order to rewrite my VFP programs in C# using LinqToSQL and a SQLExpress data file. I have things seeming to work fine by using a bindingsource and the code bindingSource1.DataSource = BBdata.LoadClient(clhld); where LoadClient is a stored procedure which selects the ClientNbr == clhld. It returns the record and all of the controls are bound and showing the data. If Read More...
|
-
I have a simple method that searches for students who's name contains a value as defined by the user. public List Student > Find( string firstName) { List Student > students = ( from s in Context.Students where s.Person.FamName.Contains( firstName ) select s ).ToList(); return students; } This works on Sql Server 2005 but does not work on Sql Express. Oddly though, if I change the query to explicitly define the search parameter it works. But this is less than usefull. List Student > students = ( Read More...
|
-
I like how LINQ to SQL will batch update operations into a single DB roundtrip when I call submitChanges(). I wonder if there is a way to achieve the same result with multiple unrelated queries. Say I have one query which hits table A, then another unrelated query that hits table B. Is there a way to perform the two requests in a single DB round-trip? This can be achieved with the traditional SqlCommand. Bear with me if this sounds silly. It is a bit. The below code is a hacky example of what I'm Read More...
|
-
Hi, I was reviewing the following blog on how to update records across multiple tables: http://weblogs.asp.net/scottgu/archive/2007/07/11/linq-to-sql-part-4-updating-our-database.aspx Can someone explain the difference between the bolded code below? It seems that Scott has 2 ways of updating the relationship for a parent/child table. The code in red bold is taking the entire class/record of chai and tofu, and assigning it to the product collection for order details. Shouldn't we only have to set Read More...
|
-
Hello, I am trying to use LINQ dynamic expressions to evaluate if an object satisfies to a set of conditions. Let's say I have a class Person with properties like Name, Age, Sex, Country, City etc. I have a set of conditions that some Person instances may satisfy and others don't. Examples of conditions: class Condition { string Name; string Predicate; object[] Values; } var conditions = new List(); conditions.Add(new Condition { "Age conditions.Add(new Condition { "Name.Contains(\"Jo\")" }; It's Read More...
|
-
Hi everyone, When you call .SubmitChanges, does it perform one UPDATE to the database, or are multiple updates sent to update all rows that have changed. I believe from reading the following blog, a set of statements are used, but does that mean a set of UPDATE for each and every record requiring updating? http://weblogs.asp.net/scottgu/archive/2007/07/11/linq-to-sql-part-4-updating-our-database.aspx In the example below, lets say 5000 records require updating (I know this isn't the actual record Read More...
|
-
Transforming Open XML documents using XSLT is an interesting scenario. However, Open XML documents are stored using the Open Packaging Convention (OPC), which are essentially ZIP files that contain XML and binary parts. XLST processors can’t open and transform such files. But if we first convert this document to a different format, the Flat OPC format, we can then transform the document using XSLT. Perhaps the most compelling reason to use XSLT on Open XML documents is document generation. You can Read More...
|
-
Transforming Open XML documents using XSLT is an interesting scenario, but before we can do so, we need to convert the Open XML document into the Flat OPC format. We then perform the XSLT transform, producing a new file in the Flat OPC format, and then convert back to Open XML (OPC) format. This post is one in a series of four posts that present this approach to transforming Open XML documents using XSLT. The four posts are: Transforming Open XML Documents using XSLT Presents an overview of the transformation Read More...
|
-
Transforming Open XML documents using XSLT is an interesting scenario, but before we can do so, we need to convert the Open XML document into the Flat OPC format. We then perform the XSLT transform, producing a new file in the Flat OPC format, and then convert back to Open XML (OPC) format. This post is one in a series of four posts that present this approach to transforming Open XML documents using XSLT. The four posts are: Transforming Open XML Documents using XSLT Presents an overview of the transformation Read More...
|
-
Transforming Open XML documents using XSLT is an interesting scenario, but before we can do so, we need to convert the Open XML document into the Flat OPC format. We then perform the XSLT transform, producing a new file in the Flat OPC format, and then convert back to Open XML (OPC) format. This post is one in a series of four posts that present this approach to transforming Open XML documents using XSLT. The four posts are: Transforming Open XML Documents using XSLT Presents an overview of the transformation Read More...
|
-
Hi, I have the following query. var query = from s in BCSDC.Q_SupplierCmptCosts where BCSDC.Q_CostScreenSearches.Any(c1 => c1.ComponentID == s.ComponentID && c1.SupplierID == s.SupplierID && c1.ProgramName.Contains(Pgm)) && s.CommodityCostSheetID == CommodityCostsheetID select s; Here Q_SupplierCmptCosts and Q_CostScreenSearches are views. What i need to happen here is that for each an every Supplier,Component ID's in Q_SupplierCmptCosts return those records from the other view.Also i need to be Read More...
|
-
I'm just learning about lambda expressions and was reading Scott's blog on Lambda expressions...and want to understand where Lambda expressions can be used. In his blog, http://weblogs.asp.net/scottgu/archive/2007/04/08/new-orcas-language-feature-lambda-expressions.aspx , he has a lambda expression within a LINQ query in sample A, and also standalone as in sample B. Why is that? What is the benefit of either scenario? Can't you achieve the same results in sample B if you include it in your LINQ query? Read More...
|
-
Can anyone walk me thru what expressions in LINQ are used for? I was reading a nice writeup from Scott http://weblogs.asp.net/scottgu/archive/2007/04/08/new-orcas-language-feature-lambda-expressions.aspx and have a tough time understanding why someone would use Expressions instead of regular lambda expressions? Read More...
|
-
I have this stored procedure that was working when i used sqlmetal in 2005, but in 2008 its now giving me this error: Warning : SQM1014: Unable to extract stored procedure 'dbo.RetrieveDocumentSearch' from SqlServer. Null or empty full-text predicate. My proc is shown below and from what i can tell its the code in red that is giving me the problem. So when I run sqlmetal with the /procs option i get the warning above. If I leave out the code in red, then its fine. But as you can tell I need it. Any Read More...
|
-
when i try to execute this: dlo.LoadWith(p => p.Comments.Where(c=>c.IsApproved)); i got this: "The expression specified must be of the form p.A, where p is the parameter and A is a property or field member. any comments? Read More...
|
-
How should I put where clause for two dimensional array using linq? For one dimensional array we can write : cmbData.DataSource = arr cmbData.DataSource = From n In arr _ Where (n > 5) _ Select I want to query two dimensional array using where clause What should I write in where clause ? Read More...
|
-
Distinct clause for single column displays “Item” as column heading. How should we specify column heading for the same ? gvLinq.DataSource = From MyEmp In db.EmployeeMasters _ Let MyCity = MyEmp.City _ Select MyCity _ Distinct This displays “item” as column heading gvLinq.DataSource = From MyEmp In db.EmployeeMasters _ Let MyCity = MyEmp.City, _ Select MyCity, MyEmp.empname _ Distinct This displays “MyCity” and “Empname” as column heading. How to specify column title for single column in select of Read More...
|
-
How to control order of column in linq? When I use following code : gvLinq.DataSource = From myEmp In db.EmployeeMasters Select myEmp.EmpName, myEmp.BranchCode, _ myEmp.Active, myEmp.accesslevel My gridview displays columns in alphabetical order and not in order mentioned in select. I want to display column in order as in select clause. How to do this ? Read More...
|
-
How to define custom column name ( column titles) for linq to sql ? I can use let clause but it doesn't allow me to include spaces in column name gvLinq.DataSource = From myEmp In db.EmployeeMasters _ Let (Employee name) = myEmp.EmpName, Branch name = myEmp.BranchCode _ Select Employee name, Branch name Read More...
|
-
Hi All! In How to query BindingSource? I wrote: I have an .xml file that I read into DataSet which has relations. I also created a form in which I operate with DataSet data through BindingSource. For some controls on the form I need to run queries such as: 1. Find the oldest date in OpenDate field; 2. Find the latest date in CloseDate field; 3. Calculate the difference between latest CloseDate and oldest OpenDate (in one query); 4. Find first row in which Id == "Problem" and Status == "Open" Currently Read More...
|
-
Hello All, Quick question about DELETE CASCADE constraint. Before SQL 2005 this was not an option you had to use a trigger. In SQL 2005 you could use it, but only on one table. For example: Table1 Pk_id varchar(10) Desc varcahr(20) Table2 Type varcahr(2) Desc varchar(20) fk_table1 varchar(10) CONSTRAINT fk_table2_table1_pk REFERENCES Table1(PK_ID) ON DELETE CASCADE Table3 Name varcahr(22) Desc varchar(20) fk_table1 varchar(10) CONSTRAINT fk_table3_table1_pk REFERENCES Table1(PK_ID) ON DELETE CASCADE Read More...
|
-
I'm wor king on a base class and I wanted to encapsulate as much of the query logic in it as possible. My thought was to use the template pattern and have a CompiledQuery member in the base class that referenced a Filter expression in the Where clause that could be overriden by child classes. It goes something like this: Code Snippet public abstract class BaseClass { protected virtual void InitializeQuery() { Query = CompiledQuery.Compile>( (MyDataContext ctx) => ctx.GetTable().Where(Filter)) } protected Read More...
|
-
Hi I am trying to type cast from integer to string. I am using two data tables in a LINQ query, noticed that the columns I am joing on, one is Numeric (getting the data from Oracle DB) other is Varchar (getting data from sql server). what is best bet to write a join on these two columns. here is the LINQ query I wrote: var linqQuery = from sql1 in sqlDS.Tables["test1"].AsEnumerable() join sql2 in sqlCategoryDS.Tables["test2"].AsEnumerable() on sql1.Field("col1") equals sql2.Field("col1") select new Read More...
|
-
Hi guys, Can someone tell me how to do the following in LINQ to SQL. I need to write a query that returns the most recent Date from a column based on a where clause, or returns null if no results were returned. In particular I want the LINQ query to populate a DateTime? variable (or leave it null if nothing was returned). In SQL it would be something like SELECT OrderDate FROM Orders WHERE CustomerID = 1 ORDER BY OrderDate DESC Clearly here we would get no date at all if customer 1 had made no orders. Read More...
|
-
Julien Chable has posted the details (in English and French ) of his installer for PowerTools for Open XML . Antonio Zamora has posted details about the award-winning scripts that he wrote using PowerTools for Open XML and the VMware Infrastructure Toolkit. Nice! Róger Bermúdez posted about a real-life scenario - using a script to send bulk mail containing Open Xml Documents to a group of people. Read More...
|
-
Hi all, I have quite complex linq queries (including a few left joins) and I just want to reuse the from clause, but with different where and select statements. Something like: private var query = from fc in DataContext.FunktionCodes join fcd in DataContext.FunktionCode_Details on fc.FunktionsCodeID equals fcd.FunktionsCodeID into fcdj from fcdjX in fcdj.DefaultIfEmpty() private IList GetFreeFunktionCode() { var query2 = this.query where fc.CANBusID == this.canbusID && fc.free== true ... } private Read More...
|
-
I'm working on a project using ASP.NET MVC that requires an n-tier setup. I have a BLL that uses LINQ to SQL which means the data I get back is disconnected. I've been attempting to figure this out and I just can't seem to without some ugly solutions. Once I retrieve an item from one of my tables if I want to update it the only thing I can come up with is using stich code (copying properties from left ro right) for every property though this creates problems if I update my tables and I have to remember Read More...
|
-
Hello,every one,i have 2 questions: 1. A "Product" table, and "PurchaseOrderDetail" table,"PurchaseOrderDetail" table's fields: purchaseorderdetailid,purchaseorderid,productid,price,quantity,totalamount. when i show the "PurchaseOrderDetail" table,i wanna show "PruductName".so How to write the Lamda? My self's solution that create a view,then load to the "viewPurchaseOrderDetail",but "PurchaseOrder" and "viewPurchaseOrderDetail" has no relations!so i think it is not a good solution. 2. How to update Read More...
|
-
I have a collection of objects and I want to use linq to remove objects from the collection based on certain criteria. When I try and remove an item i'm getting a enumeration exception. Read More...
|
-
Hi, Is there any way I can determine programmatically if a LINQ entity is attached to a context? If yes, is there also some way to retrieve a reference to that context? Thanks, Adrian Read More...
|
-
Hello. I would like to ask you for help. I have a problem with translating my sql querry to linq. Here http://janovia.ovh.org/jacek/dbSchema.jpg is a part of my database, that I take data in subquery from. The outer query is from table containing IdReviewer and IdPaper. This is my sql query which works for me fine. SELECT COUNT(IdPaper) AS Count, IdReviewer FROM ReviewerPaper WHERE (IdReviewer IN (SELECT DISTINCT ReviewerTag.IdReviewer FROM ConferenceTag INNER JOIN Tag ON ConferenceTag.IdTag = Tag.Id Read More...
|
-
Hi, I'm trying to get my head around linq but getting stuck, what im trying to do is probably really simple. I've dragged an SQL table onto a dbml form and called it CAPVehicle. Now all I want to do is select one column from that table. I have this Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim cap As New CAPVehicle Dim makes = From make In cap.CVehicle_ManText Select make End Sub I get this exception Value cannot be null. Parameter name: source What Read More...
|
-
Now this is just cool – all attendees at PDC 2008 will receive a 160GB hard drive that contains all the bits. I remember in the past, attending conferences brought (for me) an unwelcome task – collecting and organizing all of the CDs/DVDs, making sure that I got all the ones I wanted, etc. This is brilliant. Some folks have been confused by what the Software + Services PDC will be about. In this 5 minute video on Channel 9, Mike Swanson explains how Software + Services will transform how we’ll think Read More...
|
-
Hi... I created a DBML file using Visual Studio's Object Relational Designer. It created all the classes just fine. However, I don't seem to be getting the metadata for the EntitySet class. I'm working through an example in the Microsoft Press book Programming Microsoft Linq, and on page 158, they have some code that looks like this. Northwind db = new Northwind(Connections.ConnectionString); var oldCustomer = db.Customers.Single(c => c.CustomerID == "VINET"); var newCustomer = new Customer { CustomerID Read More...
|
-
I am trying to do the following SQL update via LINQ. I'm having problems creating the join with multiple conditions (among other things). UPDATE d SET d . GroupNumber = s . GroupNumber , d . TierCode = s . TierCode , d . BenefitPackage = s . BenefitPackage FROM EligRecords as d JOIN EligRecords s ON s . SubscriberSSN = d . SubscriberSSN AND s . RelationshipCode = '1' Where d . RelationshipCode <> '1' and d . FileId = 171 LINQ (so far) -------------------------------------------------------------------------------------------------------------- Read More...
|
-
Hi everyone, The following code works successfully...i'm just trying to figure out any differences between using List (Option 1) or IEnumerable (Option 2) for a LINQ query? Both store the data correctly and display in my grid fine. Option 1 List MyResult > lresult = ( from p in db.MyProducts where p.Order_Details.Count > 20 select new MyResult () { ID = p.ProductID, name = p.ProductName, numorders = p.Order_Details.Count, category = p.Category.CategoryName, catid = p.Category.CategoryID }).ToList Read More...
|
-
I'm confused when I shoud use 'var' vs IEnumerable when I'm retrieving fields from my LINQ query? It almost seems that if I am shaping my LINQ query to retrieve fields in other tables, then I should use a var (Scenario 1 below). But if I am retrieving fields within one table, then I can use IEnumerable where T is the entity class for that table (Scenario 2). Is this correct? Scenario 1 var prodResults = from p in db.MyProducts where p.Category.CategoryName.StartsWith( "B" ) select new { p.Category.CategoryName, Read More...
|
-
I have a LINQ to SQL dbml. It has been set with unidirectional, so all my entities are DataContract attributed. On my client, I create a Project. Project has a Contacts collection. I add a Contact to this collection. In the debugger, before making the service call from the client layer, I see: myProject.Contacts[0].FirstName = "John" I submit the contract back to a WCF service to save it. I can see that Project.Name = "My Project", but the Contacts are now null. So, myProject.Contacts[0].FirstName Read More...
|
-
I'm starting to learn more about LINQ...and i need to understand what is meant by this: By default the result of a query syntax expression is a variable of type IEnumerable. I was reading http://weblogs.asp.net/scottgu/archive/2007/04/21/new-orcas-language-feature-query-syntax.aspx and towards the end of his article he discusses the .ToList and .ToArray options. Do we have to cast all LINQ results to IEnumerable whenever we setup a LINQ query? Why wouldn't we simply use var instead? Are there benefits Read More...
|
-
Hi everyone, Can someone explain why I'm receiving the following error? The type or namespace name 'IEnumberable' could not be found (are you missing a using directive or an assembly reference NorthwindDataContext db = new NorthwindDataContext (); IEnumberable Category > cat = from c in db.Categories where c.CategoryName.StartsWith( "C" ) select c; //SQL doesn't run here...db not hit yet. Not until you iterate. foreach ( Category i in cat) //This is when db is hit and populates cat object! { i.CategoryName Read More...
|
-
I have following problem. I need to export data from one DB to another. It should be first exported to a file, and then exported back to the another DB. Data is rows from to tables. Rows from one table contains primary key "IDField" which is autogenerated. Rows from second table containing foreign key "IDField" which corresponds to the IDField in first table (one-to-many). When importing data from first table, new values for IDField are generated. How to update these IDs to new values in the data Read More...
|
-
I have found a few LINQ to Outlook examples/tutorials on the web, but none of them seem to actually compile. Does anybody know of a step-by-step tutorial or working sample code that I can start with? Read More...
|
-
I'm trying to get sqlmetal to behave just like the designer when it creates classes. SqlMetal doesn't add any attriubes to the propert declaraitons like like the designer is doing eg: "_BenchmarkLevel" , DbType:= "Int NOT NULL" )> _ Public Property BenchmarkLevel as Integer . . . Here is the command I'm using to call sqlmetal. sqlmetal /conn:"Data Source=xxxxxx;Initial Catalog=yyyyyyyy;Integrated Security=True" /dbml:C:\DataClasses.dbml /language:vb /sprocs /functions /pluralize /entitybase:MyBaseClass Read More...
|
-
Hello, I'm learning LINQ and have run into a problem. I created a simple query against the northwind db, and I'm shaping the fields that should be returned. The problem is when I run my foreach statement, I can't modify any of the fields in my foreach statement b/c they're anonymous. I get a " property or indexer 'AnonymousType#1. cannot be assigned to -- it is read only" error. How do I iterate through my fields and update? I know it has something to do with using var, but when I try using "MyProduct" Read More...
|
-
I would like to aggregate all products as a comma-delimited string for a given category. So if the category is "Beverages" it should return "Chai, Chang, Steeleye Stout...". I have tried using group by with this but have not been successful. Any ideas would be appreciated! Read More...
|
-
Hi, I'm new to LINQ, and I'm certain this isn't the first time this question has been asked. When I create a DBML file and create code using that particular DBML file, how can I update the DBML file periodically to reflect changes to my database? For instance, I may create a new table, new fields within tables and change the null/isnull values for the columns. Is there a way for the DBML file to update rather than deleting the dbml file? Do I have to delete the table within the dbml file and readd Read More...
|
-
Hi all, I'm using Linq to SQL to return a Student enitity from the server to the client via WCF using BasicHttpBinding. This Student entity contains an AddressId, which is a foreign key reference to the Address table. I've set the Serialization of the Linq data context to Unidirectional, which I believe kicks the DataContractSerializer in to play. So when the client receives a Student entity from the server, the Address information is lost and all that's left is the AddressId. Is there any way to Read More...
|
-
In my database I have several tables which all have a column named: _recordstatus. I want to make een Extention Method that lets me filter out the invalid recordstatus and got this so far, which is not working cause Linq cant convert the GetValue to SQL statement. Code Snippet public static IQueryable FilterStatus( this IQueryable source) { int [] _notValidStatus = new int [3] { 0, 80, 99 }; var records = source.Where(r => _notValidStatus.Contains((( int ?)r.GetType().GetProperty( "_recordstatus" Read More...
|
-
I've been all over the Internet looking for explanation or examples of using Linq to Entities in a meaningful way. Nearly every example shows doing very simple queries and displaying the data in a foreach loop. I need to insert a record into an entity that has a foreign key constraint. It's a little like this: public void AddComment( string user, int projectID, DateTime timeStamp, string comment) { try { ProjectComment projectComment = ProjectComment .CreateProjectComment(1, DateTime .Now, "my comment" Read More...
|
-
Hello Everyone, I've been working on a report for work and using it as a learning excercise for LINQ. I've figured out most of my problems, but I'm finding myself stuck on this one. I have a SQL query that uses a derived table. I'm considering doing it in a stored procedure, but I wanted to know if it's possible in LINQ. Here's the SQL: Code Snippet SELECT dbo . SAF_Subscriber_Information . Subscriber_ID , derive . agg FROM dbo . SAF_Subscriber_Information INNER JOIN dbo . view_MostRecentRetailSAF_ID Read More...
|
-
Hi, I'd trying to write a linq query that performs a left join of two tables (bills, billingitems), filters all bills by CustomerID and aggregates a column of the right table (sum on billingitems.Price). However, I can't seem to figure out the proper syntax for the left outer join. For a in inner join with aggregation I came up with this: from billtotal in ( from billingitem in Context.BillingItems group billingitem by billingitem.BillID into bigroup select new {ID = bigroup.Key, Sum = bigroup.Sum(b Read More...
|
-
I need to be able to access a Linq to Sql view ( VProductContributor) from another Linq to Sql view ( Product ) through an Association. When I drag these objects on to the designer and manually define the relationships, the code to access the view from another is not created. I thought I would be able to get around the problem by putting code in a partial class of the parent view: partial class Product { public EntitySet VProductContributor > VProductContributors { get { ProductsDataContext context Read More...
|
-
Hi, I have person collection and i want to filter them based on the filter string. Now, my problem is how to convert the LINQ Query result to strongly typed collection ? Please refer below query. Dim persons = From per In personData _ Where per.PersonName.StartsWith(txtFilter.Text.Trim) _ Select per Dim persColl As PersonCollection = persons.Cast( Of PersonCollection)() If you see the above sentence, I want to do exactly the same. But it does not work. Please help. Thanks, Sheths Read More...
|
-
|
Hi, I have a scenario where I have a class that can be pulling data from many tables. The table select depends on the user interface. I am using LINQ | |
|