473,320 Members | 1,817 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Linq Query

Hello,

I have two Lists:

A = {ID, Name} = { (Null, John), (Null, Mary), (Null, Andrew), (Null,
Peter) }

B = {ID, Name} = { (1, John), (2, Robert), (3, Angela), (4, Andrew) }

I want to find which items in A do not exist in B then:

1. Add the items to B and using an ID from the function GetID(). B
would become:

B = {ID, Name} = { (1, John), (2, Robert), (3, Angela), (4,
Andrew), (231, Mary), (45, Peter) }

2. Then update the A list, or create a new one (C), to get the created
items:

C = {ID, Name} = { (231, Mary), (45, Peter) }

How can I do this with LINQ?

I have been trying a join but with the something as "not equals" but
it does not work.

I think I might need to do this in 3 steps:

1. Get items in B that do not exist in C;
2. Insert those items in C;
3. Get those items again from C to get the created ID's with the
correspondent names.

Could someone help me out with this?

Thanks,
Miguel


Jul 4 '08 #1
15 5979
On Jul 4, 2:38*pm, shapper <mdmo...@gmail.comwrote:
I have two Lists:
In memory, or are you actually trying to do this in SQL? Some
solutions for in memory may not work via LINQ to SQL.

You can use the Except operator to find out which elements in A aren't
in B, passing in an IEqualityComparer for the items if necessary.

(Marc: if you're reading this - should we write a
ProjectionEqualityComparer along the same lines as ProjectionComparer?
Instead of "ThenBy" we could also have "And" to combine
IEqualityComparers...)

Jon
Jul 4 '08 #2
Marc: if you're reading this...

Sounds reasonable; might want an "Or" as well... or maybe go "VB" with
"AndAlso"/"OrElse" ;-p

OT: repo access playing up (had to get r259 from site); could easily be
at my end, though...

Marc
Jul 4 '08 #3
OT:
Seems fixed now; must have been me...

Marc
Jul 4 '08 #4
On Jul 4, 2:44*pm, "Jon Skeet [C# MVP]" <sk...@pobox.comwrote:
On Jul 4, 2:38*pm, shapper <mdmo...@gmail.comwrote:
I have two Lists:

In memory, or are you actually trying to do this in SQL? Some
solutions for in memory may not work via LINQ to SQL.

You can use the Except operator to find out which elements in A aren't
in B, passing in an IEqualityComparer for the items if necessary.

(Marc: if you're reading this - should we write a
ProjectionEqualityComparer along the same lines as ProjectionComparer?
Instead of "ThenBy" we could also have "And" to combine
IEqualityComparers...)

Jon
Hi,

I wrote the Lists because it was easy to show some example data to
explain my case.

Yes, these are two SQL tables. Basically it is always the same
Professors and Tags example.

I had this working in SQL but I am trying to put this in LINQ ... I
have the first part working ... now I am trying to finish this last
part.

Should I place here my tables and the full explanation?

I wrote only the lists because this is the specific problem I have
now.

Thanks,
Miguel
Jul 4 '08 #5
There is at least two solutions. The first one is to built an outer join and
test the unpreserved side for null.

Untested:

var query=
from a in A
join b in B
on a.name =b.name
into myOuter
from x in myOuter
where x.name == null
select new { a.id, a.name}
basically, it should generate SQL like:

SELECT a.id, a.name
FROM tableA AS a LEFT JOIN tableB AS b
ON a.name=b.name
WHERE b.name IS NULL
(note that the LINQ syntax clearly indicate that the WHERE clause is to be
applied to the result of the JOIN, while SQL, still doing exactly the same,
but is FAR less evident... many people ask "why are we testing if b.name IS
NULL, since we know tableB has no null under its column Name. Well, we
don't! We test the result of the join, not what is in the table, since the
SQL-WHERE clause is always logically evaluated after the JOIN).
The second solution is to use contains (untested) :
var query=
from a in A
where ! (from b in B
select b.name)
.Contains(a.name) // <-----
select new { a.id, a.name}
which should generate SQL like

SELECT a.id, a.name
FROM tableA AS a
WHERE NOT EXISTS( SELECT b.name
FROM tableB AS b
WHERE b.name = a.name)

Note that even if the generated SQL are different, the query plans may be
the same, and the query plan is what really matter, in the end.


Vanderghast, Access MVP

"shapper" <md*****@gmail.comwrote in message
news:82**********************************@8g2000hs e.googlegroups.com...
Hello,

I have two Lists:

A = {ID, Name} = { (Null, John), (Null, Mary), (Null, Andrew), (Null,
Peter) }

B = {ID, Name} = { (1, John), (2, Robert), (3, Angela), (4, Andrew) }

I want to find which items in A do not exist in B then:

1. Add the items to B and using an ID from the function GetID(). B
would become:

B = {ID, Name} = { (1, John), (2, Robert), (3, Angela), (4,
Andrew), (231, Mary), (45, Peter) }

2. Then update the A list, or create a new one (C), to get the created
items:

C = {ID, Name} = { (231, Mary), (45, Peter) }

How can I do this with LINQ?

I have been trying a join but with the something as "not equals" but
it does not work.

I think I might need to do this in 3 steps:

1. Get items in B that do not exist in C;
2. Insert those items in C;
3. Get those items again from C to get the created ID's with the
correspondent names.

Could someone help me out with this?

Thanks,
Miguel

Jul 4 '08 #6
On Jul 4, 3:11*pm, Marc Gravell <marc.grav...@gmail.comwrote:
Marc: if you're reading this...

Sounds reasonable; might want an "Or" as well... or maybe go "VB" with
"AndAlso"/"OrElse" ;-p
"Or" is pretty tricky on the hashcode side, unfortunately...

Jon
Jul 4 '08 #7
"Or" is pretty tricky on the hashcode side, unfortunately...

Yes, that occurred to me as well... oh well... the rest is simple,
fortunately - but it won't help the OP with LINQ-to-SQL...

Marc
Jul 4 '08 #8
There are problems with the Or. Note that VB AndAlso and OrElse are related
to short-circuiting, and unless I didn't get the point, this is not really
the case here (since the expression tree does not have the real values, it
cannot short-circuit anything).

The two major problems I see with the OrWhere are:

1 - they are still limited to few 'linear' cases since, in the end, you
still need to make ONE expression at the end. Start with

( a AND b) OR (a AND c)
so, you will use:

.Where( a )
.Where ( b )
.OrWhere ( ...??? ) // doom

2- if we think outside LINQ-to SQL, but sticks with LINQ to object, an
OrWhere could produce radically different result dependant of the deferred
state of the expression you are building. Indeed:

.Where( x== "USA" )
...
.OrWhere ( x== "Canada" )
if the ... got an expression forcing the evaluation of the expression, such
as .Reverse, then the OrWhere( x=="Canada" ) does nothing, since all
members still in the sequence are with x=="USA". On the other hand, if we
are lucky, and the ... does nothing bad, then, MAYBE, the OrWhere will be
equivalent to

.Where ( x=="USA" || x=="Canada" )
BUT, you have to admit (or I really got something wrong, in which case I
will be glad to know it) such construction would be really risky business
(maintenance or otherwise).


Vanderghast, Access MVP

"Marc Gravell" <ma**********@gmail.comwrote in message
news:e1**************@TK2MSFTNGP03.phx.gbl...
>Marc: if you're reading this...

Sounds reasonable; might want an "Or" as well... or maybe go "VB" with
"AndAlso"/"OrElse" ;-p

OT: repo access playing up (had to get r259 from site); could easily be at
my end, though...

Marc
Jul 4 '08 #9
On Jul 4, 3:55*pm, "Michel Walsh"
<vanderghastArobaseMsnDot...@nospam.comwrote:
There are problems with the Or. Note that VB AndAlso and OrElse are related
to short-circuiting, and unless I didn't get the point, this is not really
the case here (since the expression tree does not have the real values, it
cannot short-circuit anything).

The two major problems I see with the OrWhere are:

1 *- they are still limited to few 'linear' cases since, in the end, you
still need to make ONE expression at the end. Start with

* * ( a *AND b) *OR (a *AND c)

so, you will use:

* * .Where( *a )
* * .Where ( b )
* * .OrWhere ( ...??? * ) * *// *doom

2- if we think outside LINQ-to SQL, but sticks with LINQ to object, an
OrWhere could produce radically different result dependant of the deferred
state of the expression you are building. Indeed:

* * * * .Where( x== "USA" )
* * * * ...
* * * * .OrWhere ( x== "Canada" )

if the ... got an expression forcing the evaluation of the expression, such
as .Reverse, *then the OrWhere( x=="Canada" ) *does nothing, since all
members still in the sequence are with x=="USA". On the other hand, if we
are lucky, and the ... does nothing bad, then, MAYBE, the OrWhere will be
equivalent to

* * * * .Where ( x=="USA" || * x=="Canada" )

BUT, you have to admit (or I really got something wrong, in which case I
will be glad to know it) such construction would be really risky business
(maintenance or otherwise).

Vanderghast, Access MVP

"Marc Gravell" <marc.grav...@gmail.comwrote in message

news:e1**************@TK2MSFTNGP03.phx.gbl...
Marc: if you're reading this...
Sounds reasonable; might want an "Or" as well... or maybe go "VB" with
"AndAlso"/"OrElse" ;-p
OT: repo access playing up (had to get r259 from site); could easily beat
my end, though...
Marc
Hi,

Basically I am converting a synchronization from SQL to LINQ.

I have 3 tables:

Files FileID, Name, URL
Tags TagID, Name
FilesTags FileID, TagID

So, when I update a File and its Tags the following actions should
take place:
Get list of tags inserted by user (Input)
Check which tags from "Input" exist in table Tags
The tags that do not exist should be created in Tags
Delete all records in FilesTags given current FileID
(I drop the synchronization on this when moving from SQL
to Linq.
I just delete all tags associated to given FileID and
recreate them on next step)
Add all tags, already existing and recently created,
to FilesTags using the FileID

So I ended up with the following Linq Code:

// Get file tags
var tags = (from ft in database.FilesTags
where ft.FileID == id
select ft);

// Delete files tags
database.FileTags.DeleteAllOnSubmit(tags);

// Get input tags
List<Tagform = data.Tags.Split(',').Select(p =new Tag { Name
= p.Trim()}).ToList();

// Get existing tags
List<Tagexisting = (from t in database.Tags
join f in form on t.Name equals
f.Name
select t).ToList();

// Get notexisting tags
List<Tagnotexisting = (from f in form
where ! (from t in
database.Tags
select
t.Name).Contains(f.Name)
select new Tag {
TagID = Guid.NewGuid(),
Name = f.Name
}).ToList();

// Insert notexisting tags
database.Tags.InsertAllOnSubmit(notexisting);

// Insert files tags

database.FilesTags.InsertAllOnSubmit(existing.Conc at(notexisting));

// Submit changes
database.SubmitChanges();

I didn't tested yet because I still have some work to on on my
application (I am using Asp.NET MVC).

But in this moment I don't get any error when compiling it.

Any suggestion to improve my code?

Thank you,
Miguel
Jul 4 '08 #10
Re Or being limited to linear cases - we're talking about composition
of a predicate (or potentially : expression) - i.e. the single
argument to a single Where condition; not
successive .Where(foo).Or(bar) - instead, it is .Where(foo.Or(bar));

So it isn't a problem from *that* perspective; but the hashcode is a
real nuicance... of course for expression-based LIQN this is a
problem, and in most cases in question, maybe the hashcode isn't too
likely... at worst case, it could return a fixed constant (with a
documented warning). Yes, if it was used in a dictionary it would
suck, but if it is just used for Equals it should work. But I don't
propose doing anything ;-p

Jon - to avoid dupliaction, I have a working .And(...), but the unit
test is incomplete...

Marc
Jul 4 '08 #11
Sure, that sounds good. Another alternative will be to maintain a
metadata-flag having four possible values: as_read (from the db), new (newly
added), modified (the value changed) and delete. The hic is to be able to
update that metadata flag as the user input progresses. If that can be done,
you can then loop over this flag to know what to do in SQL about the data:
nothing, append, update, delete. That is a very crude approximation to
ADONet status, in fact. You may also substitute that metadata flag with some
kind of { old_data, new_data } : if both are equal, it is the same as our
previous flag set as_read; if only the first one is null, that is new data;
if only the last one is null, that is deleted; otherwise, it is a modified
value. Again, that assumes you can follow the user input progress, in
new_data, but you can also have a crude individual UNDO, replacing new-data
by old_data (without requering the db). That is not about LINQ, though.
Vanderghast, Access MVP

Jul 4 '08 #12
I see. Make sense now.
Vanderghast, Access MVP

Jul 4 '08 #13
Marc Gravell <ma**********@gmail.comwrote:
Jon - to avoid dupliaction, I have a working .And(...), but the unit
test is incomplete...
Righto - as well as an ProjectionEqualityComparer?

--
Jon Skeet - <sk***@pobox.com>
Web site: http://www.pobox.com/~skeet
Blog: http://www.msmvps.com/jon_skeet
C# in Depth: http://csharpindepth.com
Jul 4 '08 #14
Righto - as well as an ProjectionEqualityComparer?

Yes; pretty much a straight copy of the classes used for
ProjectionComparer, with Equals/GetHashCode instead of Compare; I just
need to get the code-coverage to that magic 100% ;-p
[probably Monday; I'm planning on a lazy weekend...]

Marc
Jul 5 '08 #15
Please go to http://www.a2zdotnet.com/Default.aspx
for step by step tutorials for beginers in LINQ sections.

--
regards,
Pankaj
http://www.A2ZDotNet.com
"shapper" wrote:
Hello,

I have two Lists:

A = {ID, Name} = { (Null, John), (Null, Mary), (Null, Andrew), (Null,
Peter) }

B = {ID, Name} = { (1, John), (2, Robert), (3, Angela), (4, Andrew) }

I want to find which items in A do not exist in B then:

1. Add the items to B and using an ID from the function GetID(). B
would become:

B = {ID, Name} = { (1, John), (2, Robert), (3, Angela), (4,
Andrew), (231, Mary), (45, Peter) }

2. Then update the A list, or create a new one (C), to get the created
items:

C = {ID, Name} = { (231, Mary), (45, Peter) }

How can I do this with LINQ?

I have been trying a join but with the something as "not equals" but
it does not work.

I think I might need to do this in 3 steps:

1. Get items in B that do not exist in C;
2. Insert those items in C;
3. Get those items again from C to get the created ID's with the
correspondent names.

Could someone help me out with this?

Thanks,
Miguel


Aug 11 '08 #16

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Marshal | last post by:
I've just had a chance to review LINQ, DLinq, and XLinq, (which I only heard about last week after the PDC). The various LINQs actually seem to live up to expectations - Using query semantics to...
28
by: Marc Gravell | last post by:
In Linq, you can apparently get a meaningful body from and expression's .ToString(); random question - does anybody know if linq also includes a parser? It just seemed it might be a handy way to...
4
by: BeSharp | last post by:
I recently stumbled across a pretty interesting LINQ to SQL question and wonder, whether anybody might have an answer. (I'm doing quite some increasing LINQ evangelism down here in Germany.). ...
1
by: 0to60 | last post by:
Let's say we have your basic Invoices and InvoiceItems table. If we load this in with LINQ: var query = from i in db.Invoices select i; When I then loop through my invoices, if I wanna...
8
by: Andy | last post by:
Hi, I'm trying to add a where clause to my query: List<stringtypes = new List<string>(); types.Add( "A" ); types.Add( "B" ); query = query.Where( c =types.Contains( c.Type ) );
0
by: =?Utf-8?B?SHlwZXJjb2Rlcg==?= | last post by:
I'm encountering some strange behavior after deploying a ASP.net 3.5 website to production, i'm unable to reproduce these in my dev environment. This error seems to occur very randomly but it's...
14
by: thj | last post by:
Hi, I was wondering what you guys are using and why? LINQ to SQL or NHibernate? Thanks in advance, Tommy
9
by: Cirene | last post by:
I'm about to begin a brand new, big, ASP.NET project (using 3.5 .net fw), VS 2008. I'm using MySQL as the backend (customer request.) I have absolutely no experience with LINQ and/or the Entity...
1
by: jbot | last post by:
I've got some pages that use linq to sql. When I look at the generated sql statements in sql server profiler, I see the same statements repeated. In the example below (from a linq data source...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.