Move files with ssh

Question

I'm trying to construct my linq-to-sql expression so that it only generates a single SQL database query. The query involves two nested selects, a simplifed verion is:

var query = from person in People 
            where person.ID == 1234
            select new PersonDetails() 
            {
               ID = person.ID,
               FirstName = person.FirstName,
               LastName = person.LastName,
               Addresses = from address in Addresses 
                           where address.PersonID == person.ID
                           select address
               PhoneNumbers = from number in PhoneNumbers
                              where number.PersonID == person.ID
                              select number
            }

The expression will result in two SQL queries being executed. The first one joins the Person and PhoneNumbers tables and pulls the required information from them. The second query is a simple select from the Addresses table. If I comment out the phone numbers property then only a single query which joins Person and Addresses gets executed so the L2S engine does recognise both the relationships.

Does anyone have any idea how I could construct the LINQ expression so that linq-to-sql would only generate a single SQL query?

Solution 1

One way would be to mount the SSH using sshfs. It's fairly quick, fairly clean and doesn't need root to action. There's a little bit of setup to do (that may actually be optional these days) but you can read about that on its help page.

But here's how I just mounted my media centre's home in a subdirectory of my local home:

oli@bert:~$ mkdir tim # create a directory to mount on
oli@bert:~$ sshfs oli@tim:/home/oli tim
oli@bert:~$ ls tim
alsa-info.sh  kernelbuild  key  NVIDIA-Linux-x86-270.26.run  uberboxee

Once you're at that point, the remote filesystem and local filesystem act as one. You can move things into the mounted volume as you like. When you're done, just fire off fusermount -u the_dir_you_mounted_on:

oli@bert:~$ fusermount -u tim
oli@bert:~$ ls tim
oli@bert:~$ # there's nothing there because there's nothing mounted on it

Solution 2

Well SCP stands for Secure Copy (Not Secure Move ;). Joking) but for what i have read in the man pages, help sites and others there is no way to use scp to "move" a file. Just copy it.

You might try: scp FILE user@domain: && rm FILE

I added the && since && will only run the next command IF the first one succeeds.

Sorry could not be more helpful with the specific scp problem. I use scp a lot too.

If you want, you could use rysnc which i find it sometimes better than scp.

You could rsync --remove-source-files FILE user@domain: which i even find it better if you loose connection to the server (big files for example) since rsync can continue with even the --partial and --progress flags

Comments

[sipsorcery]

Looks like L2S has some smarts around the let statement. If I use two let statements in my linq expression it still ends up with two separate SQL queries the first of which uses a left outer join to handle the let.

[Oli]

Worth mentioning that this is further made simple by using SSH key authentication instead of passwords. And I've made "tim" resolve to its local network IP by editing /etc/hosts.

[Lekensteyn]

If you need to specify a different port, use the -p [port] option as in sshfs oli@tim:/home/oli tim -p 1234. If you have multiple SSH keys, and do not want to create a ~/.ssh/config file first, you can use the -o IdentityFile=/path/to/id_rsa option.

[topless]

I am aware of sshfs but I was more interested in one line command. Piping as it seems its a solution but for the general interest of mine it would be funny to have smv like scp.

[topless]

+1 but I ll go with sshfs as an answer due to elegancy :)

[Oli]

You could certainly write a smv alias or a bash script to scp-then-'rm' if it's really a bother.

[Jacques]

this seems to be mixing query expressions with extension methods. Is there any way to achieve this with just query expressions?