Linq Query

[shapper]

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

 

[Jon Skeet [C# MVP]]

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

 

[Marc Gravell]

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

 

[Marc Gravell]

OT:
Seems fixed now; must have been me...

Marc

 

[Michel Walsh]

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]

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

 

[Jon Skeet [C# MVP]]

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

 

[Marc Gravell]

"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

 

[Michel Walsh]

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

 

[shapper]

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

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<Tag> form = data.Tags.Split(',').Select(p => new Tag { Name
= p.Trim()}).ToList();

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

// Get notexisting tags
List<Tag> notexisting = (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.Concat(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

 

[Marc Gravell]

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

 

[Michel Walsh]

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

 

[Michel Walsh]

I see. Make sense now.


Vanderghast, Access MVP

 

[Jon Skeet [C# MVP]]

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

Righto - as well as an ProjectionEqualityComparer?

 

[Marc Gravell]

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

 

[Pankaj]

Please go to http://www.a2zdotnet.com/Default.aspx
for step by step tutorials for beginers in LINQ sections.